2 January 2013

URL related simple MySQL functions

Another straight to the point solution

Below you can find simple MySQL code snippets to extract various information from URLs stored in a database table.

All these examples work without creating any stored procedures.

Extract server name from URL:

SELECT 
IF(
	LOCATE('http://', url) = 0, 
	'-',
	SUBSTRING_INDEX(url, '/', 3)
)
FROM TABLE_NAME

Above example will convert full URL:
http://de77.com/php/php-class-how-to-read-id3v2-tags-from-mp3-filesinto:
http://de77.comIt will return '-' when there is no 'http://' in column `url`.

Extract domain name from URL:

SELECT 
SUBSTRING_INDEX(
	SUBSTRING_INDEX(url, '/', 3),
'/', -1)
FROM TABLE_NAME

Above example will convert full URL:
http://de77.com/php/php-class-how-to-read-id3v2-tags-from-mp3-filesinto:
de77.comIt will return original value when there is no 'http://' in column `url`.

Extract filename (or last directory) from URL:

SELECT 
REVERSE(
	SUBSTRING_INDEX(REVERSE(url), '/', 1)
)
FROM TABLE_NAME

Above example will convert full URL:
http://de77.com/hello_world/files.mp3into:
files.mp3

Extract file extension from URL:

SELECT 
REVERSE(
	SUBSTRING_INDEX(REVERSE(url), '.', 1)
)
FROM t1

Above example will convert full URL:
http://de77.com/hello_world/files.mp3into:
mp3

Extract TLD from URL:

SELECT
REVERSE(
SUBSTRING_INDEX(
		REVERSE(
			SUBSTRING_INDEX(url, '/', 3)
		),
	'.', 1)
)
FROM TABLE_NAME

Above example will convert full URL:
http://de77.com/hello_world/files.mp3into:
com