Re: %CHECK and SQL

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