Re: Finding non-alphanumeric characters using SQL

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.