Re: Finding non-alphanumeric characters using SQL

["Francis Lapeyre" <flapeyre@xxxxxxxxx>]

The data in question goes back, in some cases, to the late 80's. At that
time, it was on a Wang, I think. They didn't want to standardize the format
for this database, probably because what they have in the database is what's
on the paper contract. Oh, well..

On Tue, Mar 25, 2008 at 6:00 PM, <vhamberg@xxxxxxxxxxx> wrote:

> Chuck
>
> I like this - but as one who has done a lot of screens, I prefer to stem
> the tide before it gets over the wall. In other words, put an edit in the
> program that puts up the maintenance screen - this might reduce time spent
> in the database. And it seems easier to me to do that than to check for a
> constraint error and then feed that back to the screen. Is that actually
> simple to do?
>
> Ths
> Vern
>
>  -------------- Original message ----------------------
> From: CRPence <crp@xxxxxxxxxxxxxxxxxxxx>
> >    The one-time verification was performed... But what has been done to
> > prevent a recurrence of improper data in that field?  Consider adding a
> > check constraint.  The following does not match the given one-time
> > verification, but I inferred that the comment about spaces being allowed
> > was only for trailing blanks:
> >
> >      alter table TheLib.TheFile
> >       add constraint TheLib.ck_cnmacc
> >       check ( TRANSLATE(rtrim(cnmacc)
> >               ,'SAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
> >               ,' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ')
> >               = left('AAAAAAAAAA', length(rtrim(cnmacc)))
> >             ) /* S & A must be part of valid non-blank */
> >
> >    The above prevents embedded blanks but not all-blanks.  Probably
> > all-blanks is intended to be diagnosed as invalid too?  If so, then
> > adding "AND CNMACC<>''" to the logic of the CHECK would prevent the case
> > of all-blanks [the empty string] as well.
> >
> >    If the above logic effects the desired, then the SELECT verification
> > would need to be repeated and those rows [failing the validation] must
> > be corrected, before adding the constraint.
> >
> > Regards, Chuck
> > --
> >   All comments provided "as is" with no warranties of any kind
> > whatsoever and may not represent positions, strategies, nor views of my
> > employer
> >
> > Francis Lapeyre wrote:
> > > We've already verified that there are no lowercase letters anywhere
> > > in the field in any of the databases. Without the UPPER clause, I
> > > get the same results. Thanks, though - good point.
> > >
> > > John Arnold (MFS) wrote:
> > >
> > > > <<SNIP>>
> > > >
> > > > SELECT cnmacc
> > > > FROM arstageeo/arcnm
> > > > WHERE TRANSLATE(cnmacc,' ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ')
> > > >     <> ' '
> > > >
> > > > Should work if you also want to see lower case letter values.
> --
>  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.