Re: %CHECK and SQL

Hi Doug,

I have to ideas for you:

1. TRANSLATE

Use TRANSLATE to convert your chars into another special char and look for this like in:

where translate(xyz, '$$$$$$$$$$$', 'ABCDEFGHIJK') like '%$%'

Every char in the third parameter is replaced with the corresponding character of the second parameter - and then you look for that replacement.

2. REGEXP_LIKE

As Buck already mentioned a regular expression is also a good solution:

where regexp_like(xyz, '[ABCDEFGHIJK]')

The regular expression in the square brackets is an "alternative search" - that means, it searches for 1 (one) character that is part of the list - anywhere in the string.

Regular expressions are notoriously slow ... OK maybe not so slow, but also not the fastest solution - so maybe the translate option could be faster. But regular expressions are very flexible on the other side - and also something every programmer should learn.

HTH and kind regards,
Daniel

Am 19.11.2024 um 16:06 schrieb DEnglander--- via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>:

Does anyone know if there is a function in SQL like the RPG %CHECK
function where I can tell SQL to show me a list of rows where the contents
of a column does not contain a specific list of characters?

I am trying to use SQL to tell me the existing characters in a column, so
I can add logic to an RPG update program to create an error condition to
the user when they enter a character in the column that is not one of the
predefined allowed values. This is a CHAR(5) field

Everything I see in the SQL documentation only allows that comparison
using one character at a time. Basically I am looking for "show me all the
rows in table ABC where column XYZ has characters other than "A or B or C
or D or E or F or G or H or I or J or K".

Is that possible?

Thank you,

Doug



"CONFIDENTIALITY NOTICE: This e-mail transmission (and/or the attachments accompanying it) contain confidential information belonging to the sender. The information is intended only for the use of the intended recipient. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or the taking of any action in reliance on the contents of the information is strictly prohibited. Any unauthorized interception of this transmission is illegal under the law. If you have received this transmission in error, please promptly notify the sender by reply e-mail, and then destroy all copies of the transmission."
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.