Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / Informix Topics / August 2006

Tip: Looking for answers? Try searching our database.

filter for non alphanumeric characters

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mitja Udovè - 29 Aug 2006 06:04 GMT
Hello!

I would like to check if I have in text column non alphanumeric characters.
Is it possible to do it with SQL?
Peter Hasselbach - 29 Aug 2006 11:57 GMT
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
Jonathan Leffler - 30 Aug 2006 04:37 GMT
> Mitja Udovè schrieb:
>> I would like to check if I have in text column non alphanumeric
[quoted text clipped - 7 lines]
> 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.

Signature

Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.