(Update )

MySQL: extract domain name from a URL

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'.

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');

What's next?

Not much really. But if you think this was useful to you, it might be for others also: