How can I create a custom MySQL function to decode HTML entities?

Decoding HTML entities in MySQL can be achieved by creating a custom function like the one provided below:

<code class="sql">DELIMITER $$
DROP FUNCTION IF EXISTS `HTML_UnEncode`$$
CREATE FUNCTION `HTML_UnEncode`(X VARCHAR(255)) RETURNS VARCHAR(255) CHARSET latin1 DETERMINISTIC
BEGIN

DECLARE TextString VARCHAR(255) ;
SET TextString = X ;

#quotation mark
IF INSTR( X , '&amp;quot;' )
THEN SET TextString = REPLACE(TextString, '&amp;quot;','"') ;
END IF ;

#apostrophe
IF INSTR( X , '&amp;apos;' )
THEN SET TextString = REPLACE(TextString, '&amp;apos;','"') ;
END IF ;

#ampersand
IF INSTR( X , '&amp;amp;' )
THEN SET TextString = REPLACE(TextString, '&amp;amp;','&amp;') ;
END IF ;

#less-than
IF INSTR( X , '&amp;lt;' )
THEN SET TextString = REPLACE(TextString, '&amp;lt;','<') ;
END IF ;

#greater-than
IF INSTR( X , '&amp;gt;' )
THEN SET TextString = REPLACE(TextString, '&amp;gt;','>') ;
END IF ;

#non-breaking space
IF INSTR( X , '&amp;nbsp;' )
THEN SET TextString = REPLACE(TextString, '&amp;nbsp;',' ') ;
END IF ;

#inverted exclamation mark
IF INSTR( X , '&amp;iexcl;' )
THEN SET TextString = REPLACE(TextString, '&amp;iexcl;','¡') ;
END IF ;

#cent
IF INSTR( X , '&amp;cent;' )
THEN SET TextString = REPLACE(TextString, '&amp;cent;','¢') ;
END IF ;

#pound
IF INSTR( X , '&amp;pound;' )
THEN SET TextString = REPLACE(TextString, '&amp;pound;','£') ;
END IF ;

#currency
IF INSTR( X , '&amp;curren;' )
THEN SET TextString = REPLACE(TextString, '&amp;curren;','¤') ;
END IF ;

#yen
IF INSTR( X , '&amp;yen;' )
THEN SET TextString = REPLACE(TextString, '&amp;yen;','¥') ;
END IF ;

#broken vertical bar
IF INSTR( X , '&amp;brvbar;' )
THEN SET TextString = REPLACE(TextString, '&amp;brvbar;','¦') ;
END IF ;

#section
IF INSTR( X , '&amp;sect;' )
THEN SET TextString = REPLACE(TextString, '&amp;sect;','§') ;
END IF ;

#spacing diaeresis
IF INSTR( X , '&amp;uml;' )
THEN SET TextString = REPLACE(TextString, '&amp;uml;','¨') ;
END IF ;

#copyright
IF INSTR( X , '&amp;copy;' )
THEN SET TextString = REPLACE(TextString, '&amp;copy;','©') ;
END IF ;

#feminine ordinal indicator
IF INSTR( X , '&amp;ordf;' )
THEN SET TextString = REPLACE(TextString, '&amp;ordf;','ª') ;
END IF ;

#angle quotation mark (left)
IF INSTR( X , '&amp;laquo;' )
THEN SET TextString = REPLACE(TextString, '&amp;laquo;','«') ;
END IF ;

#negation
IF INSTR( X , '&amp;not;' )
THEN SET TextString = REPLACE(TextString, '&amp;not;','¬') ;
END IF ;

#soft hyphen
IF INSTR( X , '&amp;shy;' )
THEN SET TextString = REPLACE(TextString, '&amp;shy;','­') ;
END IF ;

#registered trademark
IF INSTR( X , '&amp;reg;' )
THEN SET TextString = REPLACE(TextString, '&amp;reg;','®') ;
END IF ;

#spacing macron
IF INSTR( X , '&amp;macr;' )
THEN SET TextString = REPLACE(TextString, '&amp;macr;','¯') ;
END IF ;

#degree
IF INSTR( X , '&amp;deg;' )
THEN SET TextString = REPLACE(TextString, '&amp;deg;','°') ;
END IF ;

#plus-or-minus
IF INSTR( X , '&amp;plusmn;' )
THEN SET TextString = REPLACE(TextString, '&amp;plusmn;','±') ;
END IF ;

#superscript 2
IF INSTR( X , '&amp;sup2;' )
THEN SET TextString = REPLACE(TextString, '&amp;sup2;','²') ;
END IF ;

#superscript 3
IF INSTR( X , '&amp;sup3;' )
THEN SET TextString = REPLACE(TextString, '&amp;sup3;','³') ;
END IF ;

#spacing acute
IF INSTR( X , '&amp;acute;' )
THEN SET TextString = REPLACE(TextString, '&amp;acute;','´') ;
END IF ;

#micro
IF INSTR( X , '&amp;micro;' )
THEN SET TextString = REPLACE(TextString, '&amp;micro;','µ') ;
END IF ;

#paragraph
IF INSTR( X , '&amp;para;' )
THEN SET TextString = REPLACE(TextString, '&amp;para;','¶') ;
END IF ;

#middle dot
IF INSTR( X , '&amp;middot;' )
THEN SET TextString = REPLACE(TextString, '&amp;middot;','·') ;
END IF ;

#spacing cedilla
IF INSTR( X , '&amp;cedil;' )
THEN SET TextString = REPLACE(TextString, '&amp;cedil;','¸') ;
END IF ;

#superscript 1
IF INSTR( X , '&amp;sup1;' )
THEN SET TextString = REPLACE(TextString, '&amp;sup1;','¹') ;
END IF ;

#masculine ordinal indicator
IF INSTR( X , '&amp;ordm;' )
THEN SET TextString = REPLACE(TextString, '&amp;ordm;','º') ;
END IF ;

#angle quotation mark (right)
IF INSTR( X , '&amp;raquo;' )
THEN SET TextString = REPLACE(TextString, '&amp;raquo;','»') ;
END IF ;

#fraction 1/4
IF INSTR( X , '&amp;frac14;' )
THEN SET TextString = REPLACE(TextString, '&amp;frac14;','¼') ;
END IF ;

#fraction 1/2
IF INSTR( X , '&amp;frac12;' )
THEN SET TextString = REPLACE(TextString, '&amp;frac12;','½') ;
END IF ;

#fraction 3/4
IF INSTR( X , '&amp;frac34;' )
THEN SET TextString = REPLACE(TextString, '&amp;frac34;','¾') ;
END IF ;

#inverted question mark
IF INSTR( X , '&amp;iquest;' )
THEN SET TextString = REPLACE(TextString, '&amp;iquest;','¿') ;
END IF ;

#multiplication
IF INSTR( X , '&amp;times;' )
THEN SET TextString = REPLACE(TextString, '&amp;times;','×') ;
END IF ;

#division
IF INSTR( X , '&amp;divide;' )
THEN SET TextString = REPLACE(TextString, '&amp;divide;','÷') ;
END IF ;

#capital a, grave accent
IF INSTR( X , '&amp;Agrave;' )
THEN SET TextString = REPLACE(TextString, '&amp;Agrave;','À') ;
END IF ;

#capital a, acute accent
IF INSTR( X , '&amp;Aacute;' )
THEN SET TextString = REPLACE(TextString, '&amp;Aacute;','Á') ;
END IF ;

#capital a, circumflex accent
IF INSTR( X , '&amp;Acirc;' )
THEN SET TextString = REPLACE(TextString, '&amp;Acirc;','Â') ;
END IF ;

#capital a, tilde
IF INSTR( X , '&amp;Atilde;' ) </code>

The above is the detailed content of How can I create a custom MySQL function to decode HTML entities?. For more information, please follow other related articles on the PHP Chinese website!