MySQL query case insensitive

Question

in my table ,

I have firstname and last name ,

few names are upper case ,( ABRAHAM )

few names are lower case ,(abraham)

few names are character starting with ucword (Abraham)

So when i am doing the where condition using REGEXP '^[abc]' ,am not getting proper records,

How to change the names to lower case and use SELECT QUERY ,

SELECT * FROM `test_tbl` WHERE cus_name REGEXP '^[abc]';

This is my query, works fine if the records are lower case, but my records are intermediate ,my all cus name are not lower case , all the names are like ucword ,

So for this above query am not getting proper records display ,

Answer 1

I think you should query your database making sure that the names are lowered, suppose that name is the name you whish to find out, and in your application you've lowered it like 'abraham', now your query should be like this:

SELECT * FROM `test_tbl` WHERE LOWER(cus_name) = name

Since i dont know what language you use, I've just placed name, but make sure that this is lowered and you should retrieve Abraham, ABRAHAM or any variation of the name!

Hepe it helps!

Answer 2

You don't need regexp to search for names starting with a specific string or character.

SELECT * FROM `test_tbl` WHERE cus_name LIKE 'abc%' ; 

% is wildcard char. The search is case insensitive unless you set the binary attribute for column cus_name or you use the binary operator

SELECT * FROM `test_tbl` WHERE BINARY cus_name LIKE 'abc%' ; 

Answer 3

Have you tried:

SELECT * FROM `test_tbl` WHERE LOWER(cus_name) REGEXP '^[abc]';