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 , '&quot;' ) 
THEN SET TextString = REPLACE(TextString, '&quot;','"') ; 
END IF ; 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

#capital a, tilde 
IF INSTR( X , '&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!