CODEWITHSUNDEEP
mysqlsqlsql-order-bycase-insensitive
saran
saran

Reputation: 595

Order strings case-insensitively in MySQL

How can I order username column values irrespective of case sensitive in MySQL RDBMS?

Table structure and data :

╔══════╦════════════╦════════╗
║  id  ║  username  ║  name  ║
╠══════╬════════════╬════════╣
║    1 ║   adam     ║  test  ║
║    2 ║   Ben      ║  test1 ║
║    3 ║   Aron     ║  test2 ║
║    4 ║   charles  ║  test3 ║
╚══════╩════════════╩════════╝

Desired result :

╔══════╦════════════╗
║  id  ║  username  ║
╠══════╬════════════╣
║    1 ║   adam     ║ 
║    2 ║   Aron     ║  
║    3 ║   Ben      ║  
║    4 ║   charles  ║ 
╚══════╩════════════╝

Upvotes: 3

Views: 778

Answers (2)

potashin
potashin

Reputation: 44591

You can format username to upper or lower case in the order by clause as follows:

order by lower(username)

Upvotes: 11

Sadikhasan
Sadikhasan

Reputation: 18600

There are two solutions for case insensitive.

  • First is to use the COLLATE option : 

     SELECT * FROM table_name ORDER BY username COLLATE `latin1_general_ci`;

  • The MySQL query for case insensitive ORDER BY is to add the LOWER() to your field name :

     SELECT * FROM table_name ORDER BY LOWER(username);

Upvotes: 3

Related Questions