MySQL Query: Domain aus URL extrahieren

MySQLMySQL, Queries

SELECT 
    url,
    SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 3), '://', -1), '/', 1), '?', 1) AS domain,
    SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(url, '//', '.'), '/', 1), '.', -2) AS domain2 
FROM ( 
    SELECT       'http://testdomain.com' as url 
    UNION SELECT 'https://testdomain.com' 
    UNION SELECT 'http://testdomain.de/foo.bar/fold-test.html'
    UNION SELECT 'http://testdomain.de/foo' 
    UNION SELECT 'http://testdomain.de/?foo' 
    UNION SELECT 'https://testdomain.de/foo' 
    UNION SELECT 'https://testdomain.de/?foo' 
    UNION SELECT 'http://testdomain.de?http://anotherdomain.de'  
    UNION SELECT 'testdomain.de' 
    UNION SELECT 'testdomain.de/foo'
    UNION SELECT 'testdomain.de/foo/'
    UNION SELECT 'testdomain.de/foo?param=baz'
    UNION SELECT 'testdomain.de/foo/?param=baz'
    UNION SELECT 'testdomain.de/foo/bar'
    UNION SELECT 'testdomain.de/foo/bar/'
    UNION SELECT 'testdomain.de/foo/bar/?param=baz'
    UNION SELECT 'testdomain.de/foo/bar/three'
    UNION SELECT 'testdomain.de/foo/bar/three?u=http://anotherdomain.de'
    UNION SELECT 'http://sub.testdomain.de'
    UNION SELECT 'sub.testdomain.de/foo'
    UNION SELECT 'sub.sub.testdomain.de/foo' ) AS Testtable;

SELECT

url,

SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 3), '://', -1), '/', 1), '?', 1) AS domain,

SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(url, '//', '.'), '/', 1), '.', -2) AS domain2

FROM (

SELECT 'http://testdomain.com' as url

UNION SELECT 'https://testdomain.com'

UNION SELECT 'http://testdomain.de/foo.bar/fold-test.html'

UNION SELECT 'http://testdomain.de/foo'

UNION SELECT 'http://testdomain.de/?foo'

UNION SELECT 'https://testdomain.de/foo'

UNION SELECT 'https://testdomain.de/?foo'

UNION SELECT 'http://testdomain.de?http://anotherdomain.de'

UNION SELECT 'testdomain.de'

UNION SELECT 'testdomain.de/foo'

UNION SELECT 'testdomain.de/foo/'

UNION SELECT 'testdomain.de/foo?param=baz'

UNION SELECT 'testdomain.de/foo/?param=baz'

UNION SELECT 'testdomain.de/foo/bar'

UNION SELECT 'testdomain.de/foo/bar/'

UNION SELECT 'testdomain.de/foo/bar/?param=baz'

UNION SELECT 'testdomain.de/foo/bar/three'

UNION SELECT 'testdomain.de/foo/bar/three?u=http://anotherdomain.de'

UNION SELECT 'http://sub.testdomain.de'

UNION SELECT 'sub.testdomain.de/foo'

UNION SELECT 'sub.sub.testdomain.de/foo' ) AS Testtable;

Weiterlesen →

MySQL RAM Tables

MySQLMySQL, Optimierung, Queries

SELECT TABLE_NAME FROM `information_schema`.`partitions` WHERE `TABLE_SCHEMA` = 'TABLNAME' and `TABLE_NAME` LIKE 'ram_%'

1	SELECT TABLE_NAME FROM `information_schema`.`partitions` WHERE `TABLE_SCHEMA` = 'TABLNAME' and `TABLE_NAME` LIKE 'ram_%'

DROP TABLE IF EXISTS `ram_test`

1	DROP TABLE IF EXISTS `ram_test`

CREATE TABLE `ram_test` (  
  `fieldA` varchar(100) DEFAULT NULL,
  `fieldB` varchar(100) DEFAULT NULL,
) ENGINE=Memory DEFAULT CHARSET=utf8 PACK_KEYS=0 ROW_FORMAT=COMPACT;

CREATE TABLE `ram_test` (

`fieldA` varchar(100) DEFAULT NULL,

`fieldB` varchar(100) DEFAULT NULL,

) ENGINE=Memory DEFAULT CHARSET=utf8 PACK_KEYS=0 ROW_FORMAT=COMPACT;

INSERT INTO `ram_test` SELECT * FROM `test`

1	INSERT INTO `ram_test` SELECT * FROM `test`

Weiterlesen →

Thomas Eses

WEBDEVELOPER – PHP, JavaScript, Datenbanken…

Kategorie Tags:

Schlagwort-Archiv: Queries

MySQL Query: Domain aus URL extrahieren

MySQL RAM Tables

MySQL PROCESSLIST

Hilfreiche SQL Queries für Shopware