mysql query for alphabetical search

Question

Hi ,

How to retrieve the records those character star starting with A OR B OR C ,

I just tried this query

SELECT * FROM `test_tbl` WHERE Left(cus_name, 1) = 'a'

 ---> display all customer name starting with character a

AND i added one more condition, That is

SELECT * FROM `test_tbl` WHERE Left(cus_name, 1) = 'a' or Left(cus_name, 1) = 'b'

 --->It  displayed all customer name starting with character a and in some records middle i saw some name starting with b also, 

What i want is , i want pull out records , which names are starting with A or B or C ,

And also it should order by alphabetical order,

Also i tried this below query.

SELECT * FROM `test_tbl` WHERE Left(cus_name, 1) REGEXP '^[^a-z]+$';

The rendered records for that above is , just two records started with A,

This above question for doing the alphabetical pagination ,

Thanks

works fine, but

Answer 1

try like instead

SELECT * FROM `test_tbl` WHERE cus_name like 'a%'

Answer 2

You can try:

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

It will list all rows where cus_name starts with either a, b or c.

The regex used is ^[abc]:

• ^ : Is the start anchor
• [..] : Is the character class. So [abc] matches either an a or a b or a c. It is equivalent to (a|b|c)

The regex you were using : ^[^a-z]+$

• The first ^ is the start anchor.
• [..] is character class.
• The ^ inside the character class negates it. So [^abc] is any character other than the three listed.
• + is the quantifier for one or more.
• $ is the end anchor.

So effectively you are saying: give me all the rows where cus_name contains one or more letters which cannot be any of the lowercase letters.