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:

1.SELECT HTML_UnEncode(firstname),HTML_UnEncode(lastname) FROM users;

view source

print?

01.DELIMITER $$

02. 

03.DROP FUNCTION IF EXISTS `sourcedatabase`.`HTML_UnEncode`$$

04.CREATE DEFINER=`user`@`localhost` FUNCTION `HTML_UnEncode`(x VARCHAR(255)) RETURNS varchar(255) CHARSET latin1

05.BEGIN

06. 

07.DECLARE TextString VARCHAR(255) ;

08.SET TextString = x ;

09. 

10.#quotation mark

11.IF INSTR( x , '"' )

12.THEN SET TextString = REPLACE(TextString, '"','"') ;

13.END IF ;

14. 

15.#apostrophe

16.IF INSTR( x , ''' )

17.THEN SET TextString = REPLACE(TextString, ''','"') ;

18.END IF ;

19. 

20.#ampersand

21.IF INSTR( x , '&' )

22.THEN SET TextString = REPLACE(TextString, '&','&') ;

23.END IF ;

24. 

25.#less-than

26.IF INSTR( x , '<' )

27.THEN SET TextString = REPLACE(TextString, '<','<') ;

28.END IF ;

29. 

30.#greater-than

31.IF INSTR( x , '>' )

32.THEN SET TextString = REPLACE(TextString, '>','>') ;

33.END IF ;

34. 

35.#non-breaking space

36.IF INSTR( x , ' ' )

37.THEN SET TextString = REPLACE(TextString, ' ',' ') ;

38.END IF ;

39. 

40.RETURN TextString ;

41. 

42.END$$

43. 

44.DELIMITER ;