CODEWITHSUNDEEP
phpmysql
psanjib
psanjib

Reputation: 1299

I want to write a query which name having first character having a or b or c or c-g

I want to write a query which name having first character having a or b or c or c-g. I have list of name with alphabetical order A-Z. i want to filter the by alphabetical order in 3 steps A-G, G-M, N-Z.

By clicking A-G the record shows the name which first character starting from A-G

mysql_query("select * from users where name like "A%" or name like "B%" or name like "C%" or name like "D%" or name like "E%" or name like "F%" or name like "G%"");

But i don't want to write the like several time

so is there any easy way instead of writing like several time.

Upvotes: 0

Views: 181

Answers (3)

tCode
tCode

Reputation: 434

You can try

mysql_query("select * from users where LOWER(SUBSTR(name, 1, 1)) IN ('a','b','c','d','e','f','g')");

Upvotes: 6

Alma Do
Alma Do

Reputation: 37365

There are ways to achieve this:

First: you can do it with .. OR LIKE .. syntax, like you've described.

Second: use SUBSTRING() to pass into IN operator:

SELECT * FROM t WHERE SUBSTRING(name, 1,1) IN ('A', 'B', 'C')

or use ORD()

SELECT * FROM t WHERE ORD(SUBSTRING(name, 1,1)) BETWEEN ORD('A') AND ORD('C')

Third: use REGEXP:

SELECT * FROM t WHERE name REGEXP '^[ABC]'

Upvotes: 1

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324680

REGEXP could work well here:

SELECT ........ WHERE `name` REGEXP '^[a-g]'

Alternatively, to make better use of indexes:

... WHERE `name` < 'G'

Upvotes: 5

Related Questions