MySQL内でurlencodeする方法

MySQLの関数としてデータをURLENCODEしてくれるのはないかなーと探していた所、こんなブログポストが。

• urlencode/urldecode as MySQL stored functions

urlencode();

DELIMITER ;

DROP FUNCTION IF EXISTS urlencode;

DELIMITER |

CREATE FUNCTION urlencode (s VARCHAR(4096)) RETURNS VARCHAR(4096)
DETERMINISTIC
CONTAINS SQL
BEGIN
DECLARE c VARCHAR(4096) DEFAULT '';
DECLARE pointer INT DEFAULT 1;
DECLARE s2 VARCHAR(4096) DEFAULT '';

IF ISNULL(s) THEN
RETURN NULL;
ELSE
SET s2 = '';
WHILE pointer <= length(s) DO
SET c = MID(s,pointer,1);
IF c = ' ' THEN
SET c = '+';
ELSEIF NOT (ASCII(c) BETWEEN 48 AND 57 OR
ASCII(c) BETWEEN 65 AND 90 OR
ASCII(c) BETWEEN 97 AND 122) THEN
SET c = concat("%",LPAD(CONV(ASCII(c),10,16),2,0));
END IF;
SET s2 = CONCAT(s2,c);
SET pointer = pointer + 1;
END while;
END IF;
RETURN s2;
END;

DELIMITER ;

urldecode();

DROP FUNCTION IF EXISTS urldecode;

DELIMITER |

CREATE FUNCTION urldecode (s VARCHAR(4096)) RETURNS VARCHAR(4096)
DETERMINISTIC
CONTAINS SQL
BEGIN
DECLARE c VARCHAR(4096) DEFAULT '';
DECLARE pointer INT DEFAULT 1;
DECLARE h CHAR(2);
DECLARE h1 CHAR(1);
DECLARE h2 CHAR(1);
DECLARE s2 VARCHAR(4096) DEFAULT '';

IF ISNULL(s) THEN
RETURN NULL;
ELSE
SET s2 = '';
WHILE pointer <= LENGTH(s) DO
SET c = MID(s,pointer,1);
IF c = '+' THEN
SET c = ' ';
ELSEIF c = '%' AND pointer + 2 <= LENGTH(s) THEN
SET h1 = LOWER(MID(s,pointer+1,1));
SET h2 = LOWER(MID(s,pointer+2,1));
IF (h1 BETWEEN '0' AND '9' OR h1 BETWEEN 'a' AND 'f')
AND
(h2 BETWEEN '0' AND '9' OR h2 BETWEEN 'a' AND 'f')
THEN
SET h = CONCAT(h1,h2);
SET pointer = pointer + 2;
SET c = CHAR(CONV(h,16,10));
END IF;
END IF;
SET s2 = CONCAT(s2,c);
SET pointer = pointer + 1;
END while;
END IF;
RETURN s2;
END;

DELIMITER ;

Functionとしては動いたので、実際に正しいかどうかの検証をする