Reputation: 1099

MySQL SELECT with URL Decode

Is there a way to perform a MySQL query and have one of the columns in the output directly urldecode, rather than have PHP do it.

For example this table 'contacts' would contain,

------------------------------------
|name      |email                  |
------------------------------------
|John Smith|johnsmith%40hotmail.com|
------------------------------------

SELECT * FROM `contacts`

Would output,

John Smith | johnsmith%[email protected]

Is there something along the lines of,

SELECT name, urldecode(email) FROM `contacts`

To output,

John Smith | [email protected]

Upvotes: 22

Answers (8)

Consti P

Reputation: 455

To expand on @fela's answer:

CREATE FUNCTION `url_decode`(str VARCHAR(255) CHARSET utf8) RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
  DECLARE X  INT;
  SET X = 128;
  WHILE X  < 192 DO
    SET str = REPLACE(str, CONCAT('%C5%', HEX(X)), UNHEX(CONCAT('C5', HEX(X))));
    SET str = REPLACE(str, CONCAT('%C4%', HEX(X)), UNHEX(CONCAT('C4', HEX(X))));
    SET str = REPLACE(str, CONCAT('%C3%', HEX(X)), UNHEX(CONCAT('C3', HEX(X))));
    SET  X = X + 1;
  END WHILE;
  SET X = 32;
  WHILE X  < 127 DO
    SET str = REPLACE(str, CONCAT('%', HEX(X)), UNHEX(HEX(X)));
    SET  X = X + 1;
  END WHILE;
  SET X = 168; -- C2
  WHILE X  < 192 DO
    SET str = REPLACE(str, CONCAT('%', HEX(X)), UNHEX(CONCAT('C2', HEX(X))));
    SET  X = X + 1;
  END WHILE;
  SET X = 192; -- C3
  WHILE X  < 256 DO
    SET str = REPLACE(str, CONCAT('%', HEX(X)), UNHEX(CONCAT('C3', HEX(X-64))));
    SET  X = X + 1;
  END WHILE;
  SET X = 256; -- C4
  WHILE X  < 320 DO
    SET str = REPLACE(str, CONCAT('%', HEX(X)), UNHEX(CONCAT('C4', HEX(X-128))));
    SET  X = X + 1;
  END WHILE;
  SET X = 320; -- C5
  WHILE X  < 384 DO
    SET str = REPLACE(str, CONCAT('%', HEX(X)), UNHEX(CONCAT('C5', HEX(X-192))));
    SET  X = X + 1;
  END WHILE;
  RETURN REPLACE(str, '+', ' ');
END

See the new four while blocks -- These can decode non-standard C2, C3, C4 and C5 blocks. These blocks, that might have come from the use of escape, are HTML encodings instead of UTF encodings. @Mistdemon's answer also uses these encodings (at least C2 and C3) instead of more modern multi-byte UTF encodings (encodeURIComponent).

Upvotes: 1

fela

Reputation: 1042

Inspired by Mistdemon's answer I were using the following function:

DELIMITER $$
DROP FUNCTION IF EXISTS `url_decode`$$
CREATE FUNCTION `url_decode`(str VARCHAR(255) CHARSET utf8) RETURNS VARCHAR(255) CHARSET utf8 DETERMINISTIC
BEGIN
    DECLARE X  INT;               
    SET X = 128;
    WHILE X  < 192 DO
        SET str = REPLACE(str, CONCAT('%C5%', HEX(X)), UNHEX(CONCAT('C5', HEX(X))));
        SET str = REPLACE(str, CONCAT('%C4%', HEX(X)), UNHEX(CONCAT('C4', HEX(X))));
        SET str = REPLACE(str, CONCAT('%C3%', HEX(X)), UNHEX(CONCAT('C3', HEX(X))));
        SET  X = X + 1;                           
    END WHILE;
    SET X = 32;
    WHILE X  < 127 DO
        SET str = REPLACE(str, CONCAT('%', HEX(X)), UNHEX(HEX(X)));
        SET  X = X + 1;                           
    END WHILE;
    RETURN REPLACE(str, '+', ' ');
END$$
DELIMITER ;
SELECT url_decode('/pl/tagi/mi%C5%82o%C5%9B%C4%87');

It is good if you know what characters you can expect. In the above code it converts only single bytes and 2-bytes characters from C3, C4 and C5 ranges. For more characters you need more REPLACE iterations.

If you need to decode all utf8 characters you can use the following function. It is faster than previous one, but potentially more bugy if you have incorrectly encoded strings.

DELIMITER $$
DROP FUNCTION IF EXISTS `url_decode`$$
CREATE FUNCTION `url_decode`(str VARCHAR(255) CHARSET utf8) RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
    DECLARE end INT;
    DECLARE start INT;
    SET start = LOCATE('%', str);
    WHILE start > 0 DO
        SET end = start;
        WHILE SUBSTRING(str, end, 1) = '%' AND UPPER(SUBSTRING(str, end + 1, 1)) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F') AND UPPER(SUBSTRING(str, end + 2, 1)) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F') DO
            SET end = end + 3;
        END WHILE;
        IF start <> end THEN
            SET str = INSERT(str, start, end - start, UNHEX(REPLACE(SUBSTRING(str, start, end - start), '%', '')));
        END IF;
        SET start = LOCATE('%', str, start + 1);
    END WHILE;
    RETURN REPLACE(str, '+', ' ');
END$$
DELIMITER ;
SELECT url_decode('/bg/%D0%B8%D0%B3%D1%80%D0%B8%D1%82%D0%B5-%D0%BD%D0%B0-%D0%B3%D0%BB%D0%B0%D0%B4%D0%B0');

Upvotes: 1

Roman

Reputation: 188

My solution is to declare Stored Function to decode url encoded string:

DELIMITER $$

DROP FUNCTION IF EXISTS URL_DECODE $$

CREATE FUNCTION URL_DECODE (str text) 
RETURNS text
DETERMINISTIC
BEGIN 
    DECLARE result text;
    DECLARE ind INT DEFAULT 0;

    SET result = REPLACE(str, '+', ' ');

    WHILE ind <= 255 DO
       SET result = REPLACE(result, CONCAT('%', LPAD(LOWER(HEX(ind)), 2, 0)), CHAR(ind));
       SET result = REPLACE(result, CONCAT('%', LPAD(HEX(ind), 2, 0)), CHAR(ind));
       SET ind = ind + 1;
    END WHILE;

    RETURN result;
END$$

DELIMITER ;

And than use it next way:

SELECT URL_DECODE('johnsmith%40hotmail.com')

Upvotes: 3

PhoenixTech

Reputation: 209

My answer would be very similar to Mistdemon, but with the following changes:

(1) This line should be the top insert:

INSERT INTO urlcodemap (encoded,decoded) VALUES ("%25","%");

Otherwise a space comes out as %2520 instead of %20 since space is before %....

(2) If URL encoding for post, remove all the lines for A-Z, a-z, and 0-9. The post server can definitely read standard letters and numbers... and the whole thing does not look like gobbledy-gook.

Upvotes: 0

lsblsb

Reputation: 1352

I needed a solution when selecting data records that may contain encoded URL special chars $ & + , / : ; = ? @ (whitespace not included) and doing it like that (example code):

SELECT * FROM `table` WHERE `field` REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(`field`,'%24','$'),'%26','&'),'%2B','+'),'%2C',','),'%2F','/'),'%3A',':'),'%3B',';'),'%3D','='),'%3F','?'),'%40','@') LIKE '/example-request-uri?'

Upvotes: 1

Mistdemon

Reputation: 688

If anyone is still looking for a mysql decode function:

DROP TABLE IF EXISTS urlcodemap;

CREATE TABLE `urlcodemap` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `encoded` VARCHAR(128) NOT NULL,
  `decoded` VARCHAR(128) NOT NULL,
  UNIQUE KEY urlcodemapUIdx1(encoded),
  PRIMARY KEY (`id`)  
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- Table that contains the list of encode\decode.
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%20"," ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%21","!");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%22","""");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%23","#");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%24","$");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%25","%");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%26","&");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%27","'");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%28","(");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%29",")");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%2A","*");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%2B","+");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%2C",",");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%2D","-");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%2E",".");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%2F","/");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%30","0");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%31","1");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%32","2");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%33","3");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%34","4");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%35","5");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%36","6");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%37","7");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%38","8");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%39","9");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%3A",":");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%3B",";");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%3C","<");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%3D","=");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%3E",">");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%3F","?");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%40","@");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%41","A");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%42","B");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%43","C");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%44","D");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%45","E");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%46","F");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%47","G");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%48","H");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%49","I");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%4A","J");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%4B","K");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%4C","L");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%4D","M");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%4E","N");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%4F","O");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%50","P");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%51","Q");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%52","R");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%53","S");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%54","T");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%55","U");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%56","V");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%57","W");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%58","X");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%59","Y");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%5A","Z");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%5B","[");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%5C","\\");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%5D","]");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%5E","^");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%5F","_");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%60","`");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%61","a");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%62","b");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%63","c");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%64","d");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%65","e");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%66","f");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%67","g");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%68","h");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%69","i");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%6A","j");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%6B","k");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%6C","l");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%6D","m");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%6E","n");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%6F","o");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%70","p");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%71","q");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%72","r");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%73","s");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%74","t");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%75","u");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%76","v");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%77","w");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%78","x");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%79","y");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%7A","z");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%7B","{");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%7C","|");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%7D","}");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%7E","~");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%80","`");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%82","‚");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%83","ƒ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%84","„");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%85","…");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%86","†");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%87","‡");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%88","ˆ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%89","‰");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%8A","Š");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%8B","‹");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%8C","Œ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%8E","Ž");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%91","‘");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%92","’");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%93","“");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%94","”");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%95","•");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%96","–");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%97","—");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%98","˜");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%99","™");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%9A","š");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%9B","›");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%9C","œ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%9E","ž");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%9F","Ÿ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A1","¡");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A2","¢");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A3","£");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A4","¤");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A5","¥");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A6","¦");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A7","§");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A8","¨");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%A9","©");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%AA","ª");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%AB","«");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%AC","¬");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%AE","®");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%AF","¯");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B0","°");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B1","±");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B2","²");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B3","³");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B4","´");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B5","µ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B6","¶");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B7","·");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B8","¸");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%B9","¹");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%BA","º");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%BB","»");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%BC","¼");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%BD","½");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%BE","¾");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%BF","¿");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C0","À");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C1","Á");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C2","Â");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C3","Ã");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C4","Ä");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C5","Å");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C6","Æ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C7","Ç");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C8","È");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%C9","É");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%CA","");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%CB","Ë");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%CC","Ì");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%CD","Í");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%CE","Î");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%CF","Ï");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D0","Ð");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D1","Ñ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D2","Ò");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D3","Ó");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D4","Ô");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D5","Õ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D6","Ö");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D7","×");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D8","Ø");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%D9","Ù");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%DA","Ú");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%DB","Û");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%DC","Ü");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%DD","Ý");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%DE","Þ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%DF","ß");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E0","à");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E1","á");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E2","â");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E3","ã");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E4","ä");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E5","å");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E6","æ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E7","ç");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E8","è");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%E9","é");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%EA","ê");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%EB","ë");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%EC","ì");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%ED","í");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%EE","î");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%EF","ï");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F0","ð");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F1","ñ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F2","ò");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F3","ó");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F4","ô");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F5","õ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F6","ö");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F7","÷");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F8","ø");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%F9","ù");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%FA","ú");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%FB","û");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%FC","ü");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%FD","ý");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%FE","þ");
INSERT INTO urlcodemap (encoded,decoded) VALUES ("%FF","ÿ");

DELIMITER $$

DROP FUNCTION IF EXISTS `URLDECODER`$$

CREATE FUNCTION `URLDECODER`(str VARCHAR(4096) CHARSET utf8) RETURNS VARCHAR(4096) DETERMINISTIC
BEGIN
               DECLARE X  INT;               
               DECLARE chr VARCHAR(256);
               DECLARE chrto VARCHAR(256);
               DECLARE result VARCHAR(4096);
               SET X = 1;
               WHILE X  <= (SELECT MAX(id) FROM urlcodemap) DO
                   SET chr = (SELECT `encoded` FROM urlcodemap WHERE id = X);
                   SET chrto = (SELECT `decoded` FROM urlcodemap WHERE id = X);                
                           SET str = REPLACE(str,chr,chrto);
                           SET  X = X + 1;                           
               END WHILE;
               RETURN str;
       END$$

DELIMITER ;

Usage example

SELECT urldecoder('http://testing.com/questions/7031469/is%20this%20working');

Upvotes: 34

Paul Kenjora

Reputation: 2004

I feel like its important to answer the original question ( regardless of alternatives ), mainly because its been asked and is a valid question:

The short answer: You cannot.

There is no native SQL function for decoding URL encoded strings.

The long answer: You need to write a custom SQL function:

Encode / Decode MySQL functions: http://www.dzone.com/snippets/urlencodeurldecode-mysql

The best answer: Anything URL Encoded can be stored as UTF8.

Change your tables to UTF8 by: dumping, replacing "latin1" with "utf8", and importing. Then run a script ( just once ) such as PHP, Python, or Rails to decode your column and store it back. And now you're ready to use your DB as it was intended. No overhead.

Python code to do this:

import urllib

import MySQLdb

def decode():

  con = MySQLdb.Connect(host="127.0.0.1", port=3306, user="root", passwd="", db="hostip")
  cursor = con.cursor()

  cursor.execute("SELECT city, name FROM cityByCountry")

  for row in cursor.fetchall():
    print row
    cursor.execute("UPDATE cityByCountry SET name=%s WHERE city=%s", (urllib.unquote(row[1]), row[0]))

if __name__ == "__main__":
  decode()

Upvotes: 13

Rob

Reputation: 1099

I managed to use a seperate line of PHP code to urldecode the query response back from the SQL statement before it was passed to the json_encode which is used for the JQuery autocomplete.

Upvotes: -4

MySQL SELECT with URL Decode

Answers (8)

Related Questions