MySQL Query: Domain aus URL extrahieren

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;

Thomas Eses

WEBDEVELOPER – PHP, JavaScript, Datenbanken…

Kategorie Tags:

MySQL Query: Domain aus URL extrahieren

Folgende Artikel könnten auch interessieren