filter for non alphanumeric characters

Hello! I would like to check if I have in text column non alphanumeric characters. Is it possible to do it with SQL?

Mitja Udovè schrieb: > Hello! > > I would like to check if I have in text column non alphanumeric characters. > Is it possible to do it with SQL? > > > select lname from adr where lname [1,2] not matches '[A-Z][a-z]' Result: lname O´Brien ^eyer

Peter Hasselbach wrote: > Mitja Udovè schrieb: >> I would like to check if I have in text column non alphanumeric >> characters. Is it possible to do it with SQL? >> > select lname from adr where lname [1,2] not matches '[A-Z][a-z]'> > Result: > > lname > O´Brien > ^eyer Not really; that selects anything except a row with an upper-case letter in the first column and a lower-case letter in the second. So, for example, it collects "aZ" as invalid; it also does not reject "Hammer-Anvil"; it rejects "09" which is all alphanumeric. The question is a bit harder than it looks - doubly so if the 'text column' is a column of type TEXT (classic text blob). Assuming a regular character string type, you need to use: SELECT text_col FROM source WHERE text_col MATCHES "*[^A-Za-z0-9]*"; This searches for a string of zero or more of any sort of character, followed by a non-alphanumeric, followed by another string of zero or more of any sort of character. I think that fails on a TEXT column - but try it anyway. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/