How to Decode HTML Entities in MySQL?

Decoding HTML Entities in MySQL

HTML entities are special characters that are used to represent various symbols and characters in HTML. For example, the character " represents a quotation mark, and < represents a less-than sign.

If you have text data that contains HTML entities, you may want to decode them so that the text is displayed correctly. MySQL does not have a built-in function to decode HTML entities, but you can create a user-defined function (UDF) to do this.

Here is an example of a UDF that you can use to decode HTML entities:

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 , '"' ) 
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, '&lt;','<') ; 
END IF ;

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

#non-breaking space
IF INSTR( X , '&nbsp;' ) 
THEN SET TextString = REPLACE(TextString, '&nbsp;',' ') ; 
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;' ) 
The above is the detailed content of How to Decode HTML Entities in MySQL?. For more information, please follow other related articles on the PHP Chinese website!