Using this search & replace, you can easily extract a domain name from a string in MySQL

This trick works roughly in the same way as it does in Microsoft Excel: search for a character (one ore more '/') inside a string (`url`), remove them from the string, and calculate the difference between the string as it was before the subtraction.

If you use it in a MySQL SELECT-statement, you can do that as follows:

SELECT
`url`,
LEFT(
	RIGHT(`url`, length(`url`) - (position('//' IN `url`) +1) ),
	position('/' IN RIGHT(`url`, length(`url`) - (position('//' IN `url`)+1) ) ) -1
)
AS domain
FROM `test`

Where the replacement is called 'domain'.

Test it yourself

Use the following SQL to populate a test table with some random URLs, and use the select I provided above. See what happens:

CREATE TABLE `test` ( `url` text ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`url`) VALUES
    ('https://yoursite.com/url'),
    ('https://yoursite.com/a_page/'),
    ('http://yoursite.com/nl/something_else.html'),
    ('http://another-site.nl/pagina'),
    ('-yoursite.com/pagina')
;

Need help?

If you need more help, take a look at the related service(s): Search analytics, Technical SEO.