Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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]

like image 236
Rob Avatar asked Aug 09 '11 09:08

Rob


People also ask

How to use DECODE in MySQL query?

The MySQL DECODE() function returns empty strings if the encoded string is an empty string. The DECODE() function accepts two parameters which are the encoded string to be decoded and the password string to decode the encoded string. Syntax: DECODE(encoded_string, password_string);

How do I encode in SQL?

To encode a SQL Server identifier in a PowerShell path Use one of two methods to encode a SQL Server identifier: Specify the hexadecimal code for the unsupported character using the syntax %XX, where XX is the hexadecimal code. Pass the identifier as a quoted string to the Encode-Sqlname cmdlet.

How do I write if else in MySQL?

MySQL IF-THEN-ELSE statement The syntax looks as follows: IF condition THEN statements; ELSE else-statements; END IF; If a specified condition is: true – statements between IF-THEN and ELSE will be executed.


1 Answers

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');
like image 188
Mistdemon Avatar answered Oct 22 '22 14:10

Mistdemon