Jump to content

select where first character of field is not a letter


scarhand

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.