(Table of contents & other stuff:  )

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');
→ Call to action ←

Want more?

Then let's close the virtual gap between us by one step.