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 ;

15 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
  5. I saw a lot of website but I believe this one has got something special in it in it web design new york

    ReplyDelete
  6. Hey there! Nice stuff, please keep me posted when you post something like this! web design new york

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Aw, this is quite a nice post. In concept I have to put in place writing such as this additionally – taking time and actual effort to produce a really good article… but so what can I say… I procrastinate alot and by no means manage to get something done. brand identity design agency

    ReplyDelete
  9. Taylor Lautner Workout Routine… By the way you might want to check out this cool site I found…… san fran design firms

    ReplyDelete
  10. I’ll right away grab your rss feed as I can not find your e-mail subscription link or e-newsletter service. Do you have any? Kindly let me know so that I could subscribe. Thanks. web design la

    ReplyDelete
  11. Extensively, now this article is truly the best using this crucial subject matter. My spouse and i harmonise collectively findings and tend to thirstily expect to be your primary one way upgrades. Explaining kudos won’t only always be an adequate amount of, on your wonderful purity inside your crafting articles. For certain i will easily catch an individual’s feed maintain up to date of the the latest. Enlightening job and far becoming successful for your small business trades! Please justification the actual deficient Everyday terms currently far from a few initial language. la web design

    ReplyDelete
  12. Good day” i am doing research right now and your blog really helped me”  web design la

    ReplyDelete
  13. These kinds of Search marketing boxes normally realistic, healthy and balanced as a result receive just about every customer service necessary for some product. Link Building Services converting websites

    ReplyDelete
  14. I got what you mean , regards for posting .Woh I am pleased to find this website through google. top web design agencies

    ReplyDelete
  15. Heya just wanted to give you a brief heads up and let you know a few of the pictures aren’t loading properly. I’m not sure why but I think its a linking issue. I’ve tried it in two different web browsers and both show the same results. web design

    ReplyDelete