SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Sep 2012
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to find case insensitive duplicate values from mysql ?

    I have a table "employee" having the following data:
    id name
    1 subhajit
    2 ramesh
    3 SuBhajit

    I want to find the duplicate rows in the table. Thus I need a query which will return
    id name
    1 subhajit
    3 SuBhajit

    Note that two "subhajit" are of different cases.

    Please help me write the query. Thanks in advance.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You will need to play around with the character set and collation that has been setup upon creation of the table. I suspect that you have a case insensitive collation setup, hence, your results. You can either change the collation type in the table or change your query to convert the collation type in the group section of the query as follows:

    Code:
    mysql> create table toto (id int auto_increment primary key, name varchar(100) character set latin1 collate latin1_general_ci);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert into toto (name) values ('Ron'),('tOm'),('Tom'),('Tim'),('TOM'),('RON'),('Ron'),('Tim'),('Tom');
    Query OK, 9 rows affected (0.00 sec)
    Records: 9  Duplicates: 0  Warnings: 0
    
    mysql> select * from toto;
    +----+------+
    | id | name |
    +----+------+
    |  1 | Ron  |
    |  2 | tOm  |
    |  3 | Tom  |
    |  4 | Tim  |
    |  5 | TOM  |
    |  6 | RON  |
    |  7 | Ron  |
    |  8 | Tim  |
    |  9 | Tom  |
    +----+------+
    9 rows in set (0.00 sec)
    
    mysql> select max(id), name from toto group by name;
    +---------+------+
    | max(id) | name |
    +---------+------+
    |       7 | Ron  |
    |       8 | Tim  |
    |       9 | tOm  |
    +---------+------+
    3 rows in set (0.01 sec)
    
    mysql> select max(id), name from toto group by name collate latin1_general_cs;
    +---------+------+
    | max(id) | name |
    +---------+------+
    |       6 | RON  |
    |       7 | Ron  |
    |       8 | Tim  |
    |       5 | TOM  |
    |       9 | Tom  |
    |       2 | tOm  |
    +---------+------+
    6 rows in set (0.00 sec)

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by rcashell View Post
    You will need to play around with the character set and collation that has been setup upon creation of the table. I suspect that you have a case insensitive collation setup, hence, your results. You can either change the collation type in the table or change your query to convert the collation type in the group section of the query as follows:

    Code:
    mysql> create table toto (id int auto_increment primary key, name varchar(100) character set latin1 collate latin1_general_ci);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert into toto (name) values ('Ron'),('tOm'),('Tom'),('Tim'),('TOM'),('RON'),('Ron'),('Tim'),('Tom');
    Query OK, 9 rows affected (0.00 sec)
    Records: 9  Duplicates: 0  Warnings: 0
    
    mysql> select * from toto;
    +----+------+
    | id | name |
    +----+------+
    |  1 | Ron  |
    |  2 | tOm  |
    |  3 | Tom  |
    |  4 | Tim  |
    |  5 | TOM  |
    |  6 | RON  |
    |  7 | Ron  |
    |  8 | Tim  |
    |  9 | Tom  |
    +----+------+
    9 rows in set (0.00 sec)
    
    mysql> select max(id), name from toto group by name;
    +---------+------+
    | max(id) | name |
    +---------+------+
    |       7 | Ron  |
    |       8 | Tim  |
    |       9 | tOm  |
    +---------+------+
    3 rows in set (0.01 sec)
    
    mysql> select max(id), name from toto group by name collate latin1_general_cs;
    +---------+------+
    | max(id) | name |
    +---------+------+
    |       6 | RON  |
    |       7 | Ron  |
    |       8 | Tim  |
    |       5 | TOM  |
    |       9 | Tom  |
    |       2 | tOm  |
    +---------+------+
    6 rows in set (0.00 sec)
    I like that you did a prompt dump to a text file for your example.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    mysql is by default case-insensitive

    see http://dev.mysql.com/doc/refman/5.0/...nsitivity.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •