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.

--
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.