Maarten’s Posterous

Database filtering

Here are some queries I gathered off the Internet to filter your database when it's cluttered.

This is a way to check if your table has any duplicates in a specific field.
(src: http://mysql-tips.blogspot.com/2005/04/mysql-find-duplicate-repords-example.html)

    SELECT `email`, count(`email`) as cnt
  FROM registree
  GROUP by `email`
  HAVING cnt > 1
  ORDER BY cnt;

This is a way to remove duplicates in MySQL:
(src: http://mediakey.dk/~cc/mysql-remove-duplicate-entries/)

    ALTER IGNORE TABLE `registree` ADD UNIQUE INDEX(`email`);
   
This is how you convert fields to sentence case in MySQL. (Converts only the first letter of a field to uppercase).
(src: http://eisabainyo.net/weblog/2007/07/23/convert-to-sentence-case-in-mysql/)
   

  UPDATE `registree`
  SET `firstname` =  CONCAT(UCASE(SUBSTRING(`firstname`,1,1)),'',
  LCASE(SUBSTRING(`firstname`,2,LENGTH(`firstname`))))
   
This is how you convert fields to lower case in MySQL.
(src: http://www.tbforum.nl/thread/79221.html)

    UPDATE `registree` SET `email` = LOWER(`email`);

This is the ucfirst() equivalent for MySQL. (Converts the first letter of each word in a field to uppercase).
(src: http://forums.devshed.com/showthread.php?p=1922166#post1922166)

    DROP FUNCTION IF EXISTS proper;
  SET GLOBAL  log_bin_trust_function_creators=TRUE;
  DELIMITER |
  CREATE FUNCTION proper( str VARCHAR(128) )
  RETURNS VARCHAR(128)
  BEGIN
    DECLARE c CHAR(1);
    DECLARE s VARCHAR(128);
    DECLARE i INT DEFAULT 1;
    DECLARE bool INT DEFAULT 1;
    DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/';
    SET s = LCASE( str );
    WHILE i < LENGTH( str ) DO
      BEGIN
        SET c = SUBSTRING( s, i, 1 );
        IF LOCATE( c, punct ) > 0 THEN
          SET bool = 1;
        ELSEIF bool=1 THEN
          BEGIN
            IF c >= 'a' AND c <= 'z' THEN
              BEGIN
                SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));
                SET bool = 0;
              END;
            ELSEIF c >= '0' AND c <= '9' THEN
              SET bool = 0;
            END IF;
          END;
        END IF;
        SET i = i+1;
      END;
    END WHILE;
    RETURN s;
  END;
  |
  DELIMITER ;
   
  UPDATE `registree` SET `email` = proper(`email`);

This last one is a little labourious... But it works! If this is a bit too difficult for you, take a look at PHP's ucfirst() over here http://www.php.net/manual/en/function.ucfirst.php, since there isn't much MySQL coverage on this topic.