|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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; |