SELECT WHERE not number or letter

[BIOSTALL]

Hi 🙂

Can anyone please tell me how to select data from a database that doesnt begin with a letter or a number (eg. ~#hello#~).

Obviously i cant do:

"WHERE NOT LIKE 'A%' OR NOT LIKE 'B%' ......" coz that would take the p***.

Anyone got any suggestions please??

Many thanks, BIOSTALL

[subwayman]

WHERE field REGEXP 'regular expression defn'.

Mysql uses a simple regexp profile. Chjeck it out here:

http://dev.mysql.com/doc/mysql/en/regexp.html

[BIOSTALL]

Thanks subwayman for your reply but after reading that article, i stil cant make sense of it. Would anyone be able to show me an example??

Cheers 🙂 BIOSTALL

[subwayman]

try REGEXP '^{[A-Za-z0-9].*'}

This will match any text that doesnt have either A to Z, a to z or 0 to 9 at the first character.

Broken down:

^ as the first character of a regexp means 'at the beginning'

[A-Za-z0-9] means match one character in the ranges specified

[^A-Za-z0-9] not NOT match one character in the ranges specified

. means match (almost) any character

.* means match (almost) any character any number of times.

BTW this above can be reduced to '^[/d/w].*'.

/d is any digit and /w is any 'word character' e.g. alphabetic.

Regular expressions are VERY powerful but can also be very complex. For example: '[-+]?([0-9].)?[0-9]+' will match any floating point number (as a string). How about one with an exponent? Try this: [-+]?([0-9].)?[0-9]+([eE][-+]?[0-9]+)?

They are amazingly useful. Well worth the effort of learning the basics.