(Table of contents & other stuff:
)
)
1 min.
Extracting domain names from a URL 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. (and the url is `url`)
Usage #
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');