SQL Select Statement

[boomers]

Hi

I have a large table with allot of data in it, Im trying to find the correct SQL statement to select all the records that do not start with a letter or a number.

So some records may start with a '~' and a few '$' and even some '?'. So I would like a statement that would select all of these while leaving out any records that start with anything else, such as numbers or letters.

And on a similar theme, Im also trying to select all records that start with a number.

Ive been using the "select blah from blah where LIKE 'a%'" for each letter and ive tried to have a play and search around that statement, but still not found an answer.

Would appreciate any help on the matter

 

[Kate]

LIKE is not going to be enough.
What you need is regular expressions
http://dev.mysql.com/doc/refman/5.0/en/regexp.html

 

[mikesherov]

Being difficult to grasp, here is the regex you are looking for:

SELECT `field` FROM `table` WHERE `field` REGEXP '^[^[:alnum:]]';

For your edification, here's how it works:

the first ^, since it is outside of brackets, says match the beginning of the field.
the first [ means start defining what to match.
the second ^, since it's inside of brackets, says NOT, meaning if the thing inside this doesn't match, it really is a match, and vice versa.
[:alnum:] means any alphanumeric character
the second ] closes what is to be matched

all in all, the regular expression (REGEXP) says:
match all things that begin with something that isn't alphanumeric.

if you need to select things that start with a digit instead of things that start with a letter or digit, replace alnum with digit, and get rid of the second ^ like this:

SELECT `field` FROM `table` WHERE `field` REGEXP '^[[:digit:]]';