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!