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;