Crox-the-Wolf
5/8/2017 - 9:27 PM
MySQL function for replacing HTML entities with their actual characters
MySQL function for replacing HTML entities with their actual characters
DELIMITER $$
DROP FUNCTION IF EXISTS `database`.`HTML_UnEncode`$$
CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `HTML_UnEncode`(x VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
BEGIN
DECLARE TextString VARCHAR(255) ;
SET TextString = x ;
#quotation mark
IF INSTR( x , '"' )
THEN SET TextString = REPLACE(TextString, '"','"') ;
END IF ;
#apostrophe
IF INSTR( x , ''' )
THEN SET TextString = REPLACE(TextString, ''','"') ;
END IF ;
#ampersand
IF INSTR( x , '&' )
THEN SET TextString = REPLACE(TextString, '&','&') ;
END IF ;
#less-than
IF INSTR( x , '<' )
THEN SET TextString = REPLACE(TextString, '<','<') ;
END IF ;
#greater-than
IF INSTR( x , '>' )
THEN SET TextString = REPLACE(TextString, '>','>') ;
END IF ;
#non-breaking space
IF INSTR( x , ' ' )
THEN SET TextString = REPLACE(TextString, ' ',' ') ;
END IF ;
#inverted exclamation mark
IF INSTR( x , '¡' )
THEN SET TextString = REPLACE(TextString, '¡','¡') ;
END IF ;
#cent
IF INSTR( x , '¢' )
THEN SET TextString = REPLACE(TextString, '¢','¢') ;
END IF ;
#pound
IF INSTR( x , '£' )
THEN SET TextString = REPLACE(TextString, '£','£') ;
END IF ;
#currency
IF INSTR( x , '¤' )
THEN SET TextString = REPLACE(TextString, '¤','¤') ;
END IF ;
#yen
IF INSTR( x , '¥' )
THEN SET TextString = REPLACE(TextString, '¥','¥') ;
END IF ;
#broken vertical bar
IF INSTR( x , '¦' )
THEN SET TextString = REPLACE(TextString, '¦','¦') ;
END IF ;
#section
IF INSTR( x , '§' )
THEN SET TextString = REPLACE(TextString, '§','§') ;
END IF ;
#spacing diaeresis
IF INSTR( x , '¨' )
THEN SET TextString = REPLACE(TextString, '¨','¨') ;
END IF ;
#copyright
IF INSTR( x , '©' )
THEN SET TextString = REPLACE(TextString, '©','©') ;
END IF ;
#feminine ordinal indicator
IF INSTR( x , 'ª' )
THEN SET TextString = REPLACE(TextString, 'ª','ª') ;
END IF ;
#angle quotation mark (left)
IF INSTR( x , '«' )
THEN SET TextString = REPLACE(TextString, '«','«') ;
END IF ;
#negation
IF INSTR( x , '¬' )
THEN SET TextString = REPLACE(TextString, '¬','¬') ;
END IF ;
#soft hyphen
IF INSTR( x , '­' )
THEN SET TextString = REPLACE(TextString, '­','') ;
END IF ;
#registered trademark
IF INSTR( x , '®' )
THEN SET TextString = REPLACE(TextString, '®','®') ;
END IF ;
#spacing macron
IF INSTR( x , '¯' )
THEN SET TextString = REPLACE(TextString, '¯','¯') ;
END IF ;
#degree
IF INSTR( x , '°' )
THEN SET TextString = REPLACE(TextString, '°','°') ;
END IF ;
#plus-or-minus
IF INSTR( x , '±' )
THEN SET TextString = REPLACE(TextString, '±','±') ;
END IF ;
#superscript 2
IF INSTR( x , '²' )
THEN SET TextString = REPLACE(TextString, '²','²') ;
END IF ;
#superscript 3
IF INSTR( x , '³' )
THEN SET TextString = REPLACE(TextString, '³','³') ;
END IF ;
#spacing acute
IF INSTR( x , '´' )
THEN SET TextString = REPLACE(TextString, '´','´') ;
END IF ;
#micro
IF INSTR( x , 'µ' )
THEN SET TextString = REPLACE(TextString, 'µ','µ') ;
END IF ;
#paragraph
IF INSTR( x , '¶' )
THEN SET TextString = REPLACE(TextString, '¶','¶') ;
END IF ;
#middle dot
IF INSTR( x , '·' )
THEN SET TextString = REPLACE(TextString, '·','·') ;
END IF ;
#spacing cedilla
IF INSTR( x , '¸' )
THEN SET TextString = REPLACE(TextString, '¸','¸') ;
END IF ;
#superscript 1
IF INSTR( x , '¹' )
THEN SET TextString = REPLACE(TextString, '¹','¹') ;
END IF ;
#masculine ordinal indicator
IF INSTR( x , 'º' )
THEN SET TextString = REPLACE(TextString, 'º','º') ;
END IF ;
#angle quotation mark (right)
IF INSTR( x , '»' )
THEN SET TextString = REPLACE(TextString, '»','»') ;
END IF ;
#fraction 1/4
IF INSTR( x , '¼' )
THEN SET TextString = REPLACE(TextString, '¼','¼') ;
END IF ;
#fraction 1/2
IF INSTR( x , '½' )
THEN SET TextString = REPLACE(TextString, '½','½') ;
END IF ;
#fraction 3/4
IF INSTR( x , '¾' )
THEN SET TextString = REPLACE(TextString, '¾','¾') ;
END IF ;
#inverted question mark
IF INSTR( x , '¿' )
THEN SET TextString = REPLACE(TextString, '¿','¿') ;
END IF ;
#multiplication
IF INSTR( x , '×' )
THEN SET TextString = REPLACE(TextString, '×','×') ;
END IF ;
#division
IF INSTR( x , '÷' )
THEN SET TextString = REPLACE(TextString, '÷','÷') ;
END IF ;
#capital a, grave accent
IF INSTR( x , 'À' )
THEN SET TextString = REPLACE(TextString, 'À','À') ;
END IF ;
#capital a, acute accent
IF INSTR( x , 'Á' )
THEN SET TextString = REPLACE(TextString, 'Á','Á') ;
END IF ;
#capital a, circumflex accent
IF INSTR( x , 'Â' )
THEN SET TextString = REPLACE(TextString, 'Â','Â') ;
END IF ;
#capital a, tilde
IF INSTR( x , 'Ã' )
THEN SET TextString = REPLACE(TextString, 'Ã','Ã') ;
END IF ;
#capital a, umlaut mark
IF INSTR( x , 'Ä' )
THEN SET TextString = REPLACE(TextString, 'Ä','Ä') ;
END IF ;
#capital a, ring
IF INSTR( x , 'Å' )
THEN SET TextString = REPLACE(TextString, 'Å','Å') ;
END IF ;
#capital ae
IF INSTR( x , 'Æ' )
THEN SET TextString = REPLACE(TextString, 'Æ','Æ') ;
END IF ;
#capital c, cedilla
IF INSTR( x , 'Ç' )
THEN SET TextString = REPLACE(TextString, 'Ç','Ç') ;
END IF ;
#capital e, grave accent
IF INSTR( x , 'È' )
THEN SET TextString = REPLACE(TextString, 'È','È') ;
END IF ;
#capital e, acute accent
IF INSTR( x , 'É' )
THEN SET TextString = REPLACE(TextString, 'É','É') ;
END IF ;
#capital e, circumflex accent
IF INSTR( x , 'Ê' )
THEN SET TextString = REPLACE(TextString, 'Ê','Ê') ;
END IF ;
#capital e, umlaut mark
IF INSTR( x , 'Ë' )
THEN SET TextString = REPLACE(TextString, 'Ë','Ë') ;
END IF ;
#capital i, grave accent
IF INSTR( x , 'Ì' )
THEN SET TextString = REPLACE(TextString, 'Ì','Ì') ;
END IF ;
#capital i, acute accent
IF INSTR( x , 'Í' )
THEN SET TextString = REPLACE(TextString, 'Í','Í') ;
END IF ;
#capital i, circumflex accent
IF INSTR( x , 'Î' )
THEN SET TextString = REPLACE(TextString, 'Î','Î') ;
END IF ;
#capital i, umlaut mark
IF INSTR( x , 'Ï' )
THEN SET TextString = REPLACE(TextString, 'Ï','Ï') ;
END IF ;
#capital eth, Icelandic
IF INSTR( x , 'Ð' )
THEN SET TextString = REPLACE(TextString, 'Ð','Ð') ;
END IF ;
#capital n, tilde
IF INSTR( x , 'Ñ' )
THEN SET TextString = REPLACE(TextString, 'Ñ','Ñ') ;
END IF ;
#capital o, grave accent
IF INSTR( x , 'Ò' )
THEN SET TextString = REPLACE(TextString, 'Ò','Ò') ;
END IF ;
#capital o, acute accent
IF INSTR( x , 'Ó' )
THEN SET TextString = REPLACE(TextString, 'Ó','Ó') ;
END IF ;
#capital o, circumflex accent
IF INSTR( x , 'Ô' )
THEN SET TextString = REPLACE(TextString, 'Ô','Ô') ;
END IF ;
#capital o, tilde
IF INSTR( x , 'Õ' )
THEN SET TextString = REPLACE(TextString, 'Õ','Õ') ;
END IF ;
#capital o, umlaut mark
IF INSTR( x , 'Ö' )
THEN SET TextString = REPLACE(TextString, 'Ö','Ö') ;
END IF ;
#capital o, slash
IF INSTR( x , 'Ø' )
THEN SET TextString = REPLACE(TextString, 'Ø','Ø') ;
END IF ;
#capital u, grave accent
IF INSTR( x , 'Ù' )
THEN SET TextString = REPLACE(TextString, 'Ù','Ù') ;
END IF ;
#capital u, acute accent
IF INSTR( x , 'Ú' )
THEN SET TextString = REPLACE(TextString, 'Ú','Ú') ;
END IF ;
#capital u, circumflex accent
IF INSTR( x , 'Û' )
THEN SET TextString = REPLACE(TextString, 'Û','Û') ;
END IF ;
#capital u, umlaut mark
IF INSTR( x , 'Ü' )
THEN SET TextString = REPLACE(TextString, 'Ü','Ü') ;
END IF ;
#capital y, acute accent
IF INSTR( x , 'Ý' )
THEN SET TextString = REPLACE(TextString, 'Ý','Ý') ;
END IF ;
#capital THORN, Icelandic
IF INSTR( x , 'Þ' )
THEN SET TextString = REPLACE(TextString, 'Þ','Þ') ;
END IF ;
#small sharp s, German
IF INSTR( x , 'ß' )
THEN SET TextString = REPLACE(TextString, 'ß','ß') ;
END IF ;
#small a, grave accent
IF INSTR( x , 'à' )
THEN SET TextString = REPLACE(TextString, 'à','à') ;
END IF ;
#small a, acute accent
IF INSTR( x , 'á' )
THEN SET TextString = REPLACE(TextString, 'á','á') ;
END IF ;
#small a, circumflex accent
IF INSTR( x , 'â' )
THEN SET TextString = REPLACE(TextString, 'â','â') ;
END IF ;
#small a, tilde
IF INSTR( x , 'ã' )
THEN SET TextString = REPLACE(TextString, 'ã','ã') ;
END IF ;
#small a, umlaut mark
IF INSTR( x , 'ä' )
THEN SET TextString = REPLACE(TextString, 'ä','ä') ;
END IF ;
#small a, ring
IF INSTR( x , 'å' )
THEN SET TextString = REPLACE(TextString, 'å','å') ;
END IF ;
#small ae
IF INSTR( x , 'æ' )
THEN SET TextString = REPLACE(TextString, 'æ','æ') ;
END IF ;
#small c, cedilla
IF INSTR( x , 'ç' )
THEN SET TextString = REPLACE(TextString, 'ç','ç') ;
END IF ;
#small e, grave accent
IF INSTR( x , 'è' )
THEN SET TextString = REPLACE(TextString, 'è','è') ;
END IF ;
#small e, acute accent
IF INSTR( x , 'é' )
THEN SET TextString = REPLACE(TextString, 'é','é') ;
END IF ;
#small e, circumflex accent
IF INSTR( x , 'ê' )
THEN SET TextString = REPLACE(TextString, 'ê','ê') ;
END IF ;
#small e, umlaut mark
IF INSTR( x , 'ë' )
THEN SET TextString = REPLACE(TextString, 'ë','ë') ;
END IF ;
#small i, grave accent
IF INSTR( x , 'ì' )
THEN SET TextString = REPLACE(TextString, 'ì','ì') ;
END IF ;
#small i, acute accent
IF INSTR( x , 'í' )
THEN SET TextString = REPLACE(TextString, 'í','í') ;
END IF ;
#small i, circumflex accent
IF INSTR( x , 'î' )
THEN SET TextString = REPLACE(TextString, 'î','î') ;
END IF ;
#small i, umlaut mark
IF INSTR( x , 'ï' )
THEN SET TextString = REPLACE(TextString, 'ï','ï') ;
END IF ;
#small eth, Icelandic
IF INSTR( x , 'ð' )
THEN SET TextString = REPLACE(TextString, 'ð','ð') ;
END IF ;
#small n, tilde
IF INSTR( x , 'ñ' )
THEN SET TextString = REPLACE(TextString, 'ñ','ñ') ;
END IF ;
#small o, grave accent
IF INSTR( x , 'ò' )
THEN SET TextString = REPLACE(TextString, 'ò','ò') ;
END IF ;
#small o, acute accent
IF INSTR( x , 'ó' )
THEN SET TextString = REPLACE(TextString, 'ó','ó') ;
END IF ;
#small o, circumflex accent
IF INSTR( x , 'ô' )
THEN SET TextString = REPLACE(TextString, 'ô','ô') ;
END IF ;
#small o, tilde
IF INSTR( x , 'õ' )
THEN SET TextString = REPLACE(TextString, 'õ','õ') ;
END IF ;
#small o, umlaut mark
IF INSTR( x , 'ö' )
THEN SET TextString = REPLACE(TextString, 'ö','ö') ;
END IF ;
#small o, slash
IF INSTR( x , 'ø' )
THEN SET TextString = REPLACE(TextString, 'ø','ø') ;
END IF ;
#small u, grave accent
IF INSTR( x , 'ù' )
THEN SET TextString = REPLACE(TextString, 'ù','ù') ;
END IF ;
#small u, acute accent
IF INSTR( x , 'ú' )
THEN SET TextString = REPLACE(TextString, 'ú','ú') ;
END IF ;
#small u, circumflex accent
IF INSTR( x , 'û' )
THEN SET TextString = REPLACE(TextString, 'û','û') ;
END IF ;
#small u, umlaut mark
IF INSTR( x , 'ü' )
THEN SET TextString = REPLACE(TextString, 'ü','ü') ;
END IF ;
#small y, acute accent
IF INSTR( x , 'ý' )
THEN SET TextString = REPLACE(TextString, 'ý','ý') ;
END IF ;
#small thorn, Icelandic
IF INSTR( x , 'þ' )
THEN SET TextString = REPLACE(TextString, 'þ','þ') ;
END IF ;
#small y, umlaut mark
IF INSTR( x , 'ÿ' )
THEN SET TextString = REPLACE(TextString, 'ÿ','ÿ') ;
END IF ;
RETURN TextString ;
END$$
DELIMITER ;