Mysql function to get querystring parameter of url

Not suprisingly, most google results only returned things for doing this within some kind of PHP application. ┬áProbably because nobody belives in MySQL as a data warehouse, or a place to parse strings… In the do with what you have environment of a startup, I do not have that luxury. ┬áThis function so far works decent, it’s not extremely robust, but it does a job.

 

CREATE FUNCTION `fn_getparam`(param varchar(55), url varchar(2048)) RETURNS varchar(2048) CHARSET latin1 COLLATE latin1_general_cs
BEGIN

declare val varchar(2048);
declare _param varchar(60) DEFAULT CONCAT(param,'=');

select
case
	when locate(concat('&',_param), url) > 0
		then right(url, length(url) - (locate(concat('&',_param),url)+length(concat('&',_param))-1))
	when locate(concat('?',_param), url) > 0
		then right(url, length(url) - (locate(concat('?',_param),url)+length(concat('?',_param))-1))
	when locate(concat('#',_param), url) > 0
		then right(url, length(url) - (locate(concat('#',_param),url)+length(concat('#',_param))-1))
	when locate(_param,url) > 0
		then right(url, length(url) - (locate(_param,url)+length(_param)-1) )
else null
end
into val;

set val = replace(replace(left(val, locate('&',concat(val,'&'))-1),'%20',' '),'+',' ');

RETURN val;
END

Leave a Reply

Your email address will not be published. Required fields are marked *