a few words about web development

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-files
into:
http://de77.com
It 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-files
into:
de77.com
It 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.mp3
into:
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.mp3
into:
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.mp3
into:
com

Comments

There are no comments yet