Re: Finding non-alphanumeric characters using SQL

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.