[SOLVED] Selecting by letter and non-alphabetical letter

[Prodigal Son]

I have a table of data that I'm trying to fetch out alphabetically from. Sort of like a A-Z list where you click a letter and results starting with that letter are shown.

 

So I have:

SELECT * FROM table WHERE column LIKE 'a%'

for items that start with a.

My first question is that is there any difference from using

SELECT * FROM table WHERE SUBSTRING(column,1,1) = 'a'

I checked the explain and they both looked the same. But it seems like substring is faster? Does one use less resources or is faster?

 

My second question is how can I fetch results that don't start with a-z. The closest I've gotten is:

SELECT * FROM table WHERE column NOT BETWEEN 'A' AND 'Z'

but that will also show results starting with Z. So if I do

SELECT * FROM table WHERE column NOT BETWEEN 'A' AND 'Z' AND column NOT LIKE 'Z%'

it'll work, but I'm sure there would be a better way to do it?

 

Thanks.

[TeNDoLLA]

You could use this for matching non alphabets

SELECT nick FROM users WHERE nick NOT REGEXP '^[a-z]'

[Prodigal Son]

You could use this for matching non alphabets

SELECT nick FROM users WHERE nick NOT REGEXP '^[a-z]'

Thanks, that works great. Didn't know you could use regexp with mysql.

 

Anyone have a suggestion for using LIKE vs SUBSTRING? Or would use either one be fine?

[corbin]

If you have an index on the field, LIKE 'a%' should be faster.