Re: %CHECK and SQL

[Stephen Landess <steve_landess@xxxxxxxxxxx>]

Doug -

I created a SQL UDF to test for "Bad" characters:

--------------------------------------------------------------------------------
--
--  Author:  S. Landess
--  Date:  2024-09-16
-- Purpose:  User-defined function used to identify invalid characters in Alphanumeric fields
--    characters NOT found in the list below are translated to a blank,
--  Filters out the "good" characters and return only the invalid ones
-- This UDF can only be used to check one field at a time for what I consider to be "bad" characters:
--
--------------------------------------------------------------------------------
--

  DROP              FUNCTION QGPL.BAD_CHARS;

CREATE OR REPLACE FUNCTION QGPL.BAD_CHARS
( @CHARVAL_IN VARCHAR(256))
RETURNS VARCHAR(256)
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
NOT FENCED
SET OPTION COMMIT=*NONE
BEGIN
    RETURN TRIM(TRANSLATE(@CHARVAL_IN , ' '
    ,'®°ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqqrstuvwxyz~`¦|"''!@#$%^&*()-=+[]{};:.,<>?/0123456789/\_'))
;
END


Example of usage against the JDE Item master file:

SELECT
rrn(x), IMITM, IMLITM, 'IMDSC1', IMDSC1 ,
HEX(BAD_CHARS( IMDSC1 ))
FROM F4101 x
WHERE LENGTH(TRIM(BAD_CHARS( IMDSC1 )))> 0

RRN ( X )    Short          2nd Item             Constant value      Description                                  HEX
            Item No          Number
            --------  --------------------                       --------------------
  110,360   98814828  98814828                       IMDSC1      Tube Made of Teflon® PTFE,      AF
  110,362   98814830  98814830                       IMDSC1      Tube Made of Teflon® PTFE,      AF
  114,290   46080002  46080002                       IMDSC1      2 Panel OS w/ 10  Non-Adj    *  1A
  114,291   46080012  46080012                       IMDSC1      2 Panel OS w/ 20  Non-Adj    *  1A
  114,292   46080022  46080022                       IMDSC1      3 Panel OS w/ 20  Non-Adj    *  1A
  114,657   98818902  98818902                       IMDSC1      Black Delrin® Acetal Resin Bar  AF




Regards,
Steve Landess
(512) 289-0387

________________________________
Doug Englander wrote:
 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