Page 1 of 1

MySQL Search Query, Anything but Letters

Posted: Mon Apr 24, 2006 7:30 pm
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.

Posted: Mon Apr 24, 2006 7:33 pm
by feyd

Code: Select all

`field` REGEXP '^[a-zA-Z]'

Posted: Mon Apr 24, 2006 7:38 pm
by Benjamin
Thank you sir, would have taken me bit to find that.

Posted: Mon Apr 24, 2006 8:13 pm
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?

Posted: Mon Apr 24, 2006 8:25 pm
by n00b Saibot
maybe your version of mysql doesn't support regexp's. see mysql online documentation site for more details...

Posted: Mon Apr 24, 2006 8:26 pm
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

Posted: Tue Apr 25, 2006 12:35 am
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.

Posted: Tue Apr 25, 2006 12:38 am
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 ([)

Posted: Tue Apr 25, 2006 12:41 am
by Benjamin
:) That did the trick, Thank you.