nix, shell, perl, php, mysql and mac os x tips and tricks

Monday, June 14, 2010

MySQL function for replacing HTML entities with their actual characters

For a more comprehensive function containing more special characters see http://forums.mysql.com/read.php?98,246527

Actually, better use that link (above). Blogger is messing with the code below.

Usage:
SELECT HTML_UnEncode(firstname),HTML_UnEncode(lastname) FROM users;

DELIMITER $$

DROP FUNCTION IF EXISTS `sourcedatabase`.`HTML_UnEncode`$$
CREATE DEFINER=`user`@`localhost` 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 ;

RETURN TextString ;

END$$

DELIMITER ;

4 comments:

  1. Hello ,
    I am Getting following Error..
    Can you please help..
    Thanks in advance
    Error

    SQL query:

    DELIMITER $$ DROP FUNCTION IF EXISTS `sourcedatabase`.`HTML_UnEncode` $$ CREATE DEFINER = `user`@`localhost` FUNCTION `HTML_UnEncode` (
    x VARCHAR( 255 )
    ) RETURNS varchar( 255 ) CHARSET latin1 BEGIN DECLARE TextString VARCHAR( 255 ) ;

    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$

    DROP FUNCTION IF EXISTS `sourcedatabase`.`HTML_UnEncode`$$
    CREA' at line 1

    ReplyDelete
  2. 30
    down vote
    accepted
    There are two ways to fix this:

    Execute the following in the MySQL console:

    SET GLOBAL log_bin_trust_function_creators = 1;

    Add the following to the mysql.ini configuration file:

    log_bin_trust_function_creators = 1

    and replace sourcedatabase with your db name and user with your username

    ReplyDelete
  3. #1582 - Incorrect parameter count in the call to native function 'INSTR'

    ReplyDelete
  4. To link to a website from a picture in an email or forum, you should be able to use the editing buttons in the same way you link text. css forums

    ReplyDelete