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 ;

Tuesday, June 8, 2010

Find duplicate records in MySQL

SELECT email, 
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )