Re: Finding non-alphanumeric characters using SQL

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.