Sorting when using SELECT statement

[jarvi]

I use something like the following to just return the records where my_field starts with the letter A.

$result = mysql_query('SELECT * FROM my_table WHERE my_field LIKE "A%" ORDER BY my_field');

This works fine for all the letters in the alphabet but how do I do a catch all for all the others? Surely there is a better way than doing a "not like" for each of the letters of the alphabet.

Thanks,

[mudnik]

Hi Jarvi,
what is the output that you want exactly? Not sure what you mean by catch all. You can try using a sub-querry,

[jarvi]

Sorry, by catch all I mean anything in my_field that doesn't start with a letter of the alphabet. That is anything except A-Z, so, things such as numbers and punctuation.

[digitalpoint]

If you wanted all the others, wouldn't it just be a NOT LIKE 'A%'?

[jarvi]

What I'm trying to avoid is having to put NOT LIKE "A%" and NOT LIKE "B%" AND NOT LIKE "C%" etc for all the letters of the alphabet. I guess I'm looking hoping there is someway to specify all the letters of the alphabet without having to put them all in the SELECT statement.

[draculus]

You can try

WHERE (my_field < "A") OR ((my_field > "Z") AND (my_field < "a")) OR (my_field > "z")

[mudnik]

hmmm..did the solution by draculus worked? It looked ok to me..

Would something like this work? NOT LIKE ('[a-z]%')
Can't remember already..