Re: Finding non-alphanumeric characters using SQL

[Alan Shore <AlanShore@xxxxxxxx>]

Look at the translate function (XLATE I believe) to translate the
individual characters
0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ  each to space.
That means if the result is ALL blank, then you have no problems, however
if the result is NOT blank, then you have a problem
Unfourtunately - at the moment I'm unable to do any searches to help you
out with the syntax



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill


                                                                       
             "Francis Lapeyre"                                         
             <flapeyre@xxxxxxx                                         
             om>                                                        To
             Sent by:                  "Midrange Discussion"           
             midrange-l-bounce         <midrange-l@xxxxxxxxxxxx>       
             s@xxxxxxxxxxxx                                             cc
                                                                       
                                                                   Subject
             03/25/2008 12:52          Finding non-alphanumeric characters
             PM                        using SQL                       
                                                                       
                                                                       
             Please respond to                                         
             Midrange Systems                                          
                 Technical                                             
                Discussion                                             
             <midrange-l@midra                                         
                 nge.com>                                              
                                                                       
                                                                       




List,

I'm scratching my head here. I'm trying to find all intances where a
particular field does not have a space, a number, or an uppercase letter in
it.

Using STRSQL, this does not work:


SELECT cnmacc FROM arstageeo/arcnm WHERE cnmacc not like('%
0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ%')

Niether does this:

SELECT cnmacc FROM arstageeo/arcnm WHERE trim(cnmacc) not in('%1%') and
trim(cnmacc) not in('%2%') and trim(cnmacc) not in('%3%') and trim(cnmacc)
not in('%4%') and trim(cnmacc) not in('%5%') and trim(cnmacc) not in('%6%')
and trim(cnmacc) not in('%7%') and trim(cnmacc) not in('%8%') and
trim(cnmacc) not in('%9%') and trim(cnmacc) not in('%0%') and trim(cnmacc)
not in('%A%') and trim(cnmacc) not in('%B%') and trim(cnmacc) not in('%C%')
and

(continuing the alpahbet) ...

trim(cnmacc) not in('%Z%')

This is a one-shot deal. We want to identify accounts in CNMACC (a 10-byte
fixed-length character field) which were entered with "funny" characters.

What am I doing wrong?

Thanks in advance.


--
Francis Lapeyre

Da mihi sis crustum Etruscum cum omnibus in eo.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.