mysql query between first letters ???

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
giliat
Forum Commoner
Posts: 28
Joined: Sun May 20, 2007 2:00 pm

mysql query between first letters ???

Post by giliat »

hello
i need to get the result from city table ,but i need the result to get the citys that the firs leter in between:

A-E
F-J
K-O
P-T
U-Z

to get the first leter i use
$query = "SELECT * FROM uk_area WHERE name LIKE 'A_%_%'";
but i dont whant to ad AND ... AND untill E
i like to use somthing like BETWEEN

but i dont know :cry:
i did this but i got error
$query = "SELECT * FROM uk_area WHERE name BETWEEN LIKE 'A_%_%' AND LIKE 'E_%_%'";

can someone let me know what to do :?:

THANKS
giliat
Forum Commoner
Posts: 28
Joined: Sun May 20, 2007 2:00 pm

i got it but...

Post by giliat »

i got it i use

$query = "SELECT * FROM uk_area WHERE name > 'A' AND name <'D'";

but i cant do <= 'D'

its dosent show th d unless i put < 'E'
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

This is because 'Detroit' is not equal to 'D', however it is less than E. You should use string functions to parse out the first letter from the city, then do your comparison.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Code: Select all

SUBSTR(`column`, 0, 1)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

superdezign wrote:

Code: Select all

SUBSTR(`column`, 0, 1)
I use

Code: Select all

...WHERE LEFT(`city`,1) IN ('A','B','C','D')...
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

so many alternatives.. 1 char index on city with handle read probably the fastest
REGEXP is also useful for char listing, then you could specify range [A-DX-Z] etc etc
where IN..
Post Reply