×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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
As an Amazon Associate we earn from qualifying purchases.
This thread ...
RE: Finding non-alphanumeric characters using SQL, (continued)
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact
[javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
