select where first character of field is not a letter

[scarhand]

lets say i have a table with the following in the "colors" field:

 

3 reds

red

orange

4  blues

$%$ blues

 

i need a query that will select the 3 rows where the first character of "colors" is not an actual letter, so the results returned should be:

 

3 reds

4  blues

$%$ blues

 

i have no idea how to do this. i wrote a query that selects by the first character if it is a letter.....but im stumped with this.

[Maq]

I hate using regex in MySQL but you could use REGEXP to match these requirements.

 

There may be a better way with string functions, but I can't think of any.

[Pikachu2000]

I believe this will work. If it isn't right, I'm sure someone will point it out, but give it a try.

 

SELECT `field` FROM `table` WHERE `field` NOT REGEXP '[:alpha:]'

[scarhand]

heres a better of what i'm trying to do using your example pikachu

 

"select colors, count(*) as count from `table` where substring(`colors`, 1, 1) NOT REGEXP '[:alpha:]' group by `colors` order by `colors` asc";

 

though this is not working, you can see what im trying to do

[scarhand]

heres a better example

 

234 soap

#@$ orange

red

turtles

 

i want the query to select the first 2 items based on the fact that the first character in the string is not an alpha (letter a-z).

[scarhand]

ok so this works:

 

"select * from `colors` where `color` REGEXP '^[^[:alpha:]]'";

 

however i need to group them and do a count, and this doesn't work:

 

select color, color_slug, count(*) as count from `colors` where `color` REGEXP '^[^[:alpha:]]' group by `color` order by `color` asc

 

all it does is seem to group all of the rows together if they match the REGEXP.

[fenway]

So group by the same expression.