Re: Finding non-alphanumeric characters using SQL

[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