Re: Finding non-alphanumeric characters using SQL

["Francis Lapeyre" <flapeyre@xxxxxxxxx>]

Elvis,

That works like a charm! Thanks!

On Tue, Mar 25, 2008 at 12:24 PM, Elvis Budimlic <
ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

> I think you're best off TRANSLATing all valid characters to blanks and
> then
> comparing if field is all blanks or not.  If not, you've hit upon a field
> with funny data.  Here's an example:
>
> SELECT cnmacc
> FROM arstageeo/arcnm
> WHERE TRANSLATE(UPPER(cnmacc),' ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ')
>      <> ' '
>
> Something like that ought to work.
>
> HTH, Elvis
>
> Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
> www.centerfieldtechnology.com
>
>
> -----Original Message-----
> Subject: Finding non-alphanumeric characters using SQL
>
> 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
>
>  --
> 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.