Page 1 of 1

mysql query between first letters ???

Posted: Tue Jul 24, 2007 1:40 am
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

i got it but...

Posted: Tue Jul 24, 2007 2:25 am
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'

Posted: Tue Jul 24, 2007 10:50 am
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.

Posted: Tue Jul 24, 2007 11:21 am
by superdezign

Code: Select all

SUBSTR(`column`, 0, 1)

Posted: Fri Jul 27, 2007 5:01 pm
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')...

Posted: Sat Jul 28, 2007 6:43 pm
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..