MySQL Search Query, Anything but Letters

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

MySQL Search Query, Anything but Letters

Post by Benjamin »

Can someone please show me the syntax for a query which excludes all entries which do not start with a letter. (a-z/A-Z)

Thank you.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

`field` REGEXP '^[a-zA-Z]'
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Thank you sir, would have taken me bit to find that.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Ok I tried...

Code: Select all

$Query = "SELECT `RecordID`, `Word` REGEXP '^[a-zA-Z]' FROM `TABLE` ORDER BY `Word` asc";
And..

Code: Select all

$Query = "SELECT `RecordID`, `Word` FROM `TABLE` WHERE `Word` REGEXP '^[a-zA-Z]'  ORDER BY `Word` asc";
Neither of them work and I can't find any examples online. What am I doing wrong?
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

maybe your version of mysql doesn't support regexp's. see mysql online documentation site for more details...
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Actually I got it working, but it's doing the opposite of what it is supposed to do, it's excluding everything that doesn't start with the letters a-z
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

feyd wrote:

Code: Select all

`field` REGEXP '^[a-zA-Z]'
I think this is doing the opposite of what it needs to do. This is returning all results that DO have the letters a-z as the first character. I tried searching for RegEx that would fix it but had no luck.
Last edited by Benjamin on Tue Apr 25, 2006 12:38 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I could have swore you asked for ones that start with a letter. Oh well. Add a caret (^) to immediately following the left bracket ([)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

:) That did the trick, Thank you.
Post Reply