Using this search & replace, you can easily extract a domain name from a string in MySQL. This works the same way as in Microsoft Excel: search for a character (one or more '/') inside a string (`url`), remove them from the string, and calculate the difference in length between the strings before and after the subtraction.
If you translate the Excel way into a MySQL SELECT-statement, it will look 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'.
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');
If you think this was useful to you, it might be for others also. Feel free to share: