Does your application has to check cerrtain authorzation id’s for certain access rights needed? Following SQL shows some ways how to proceed:

-- any userid between L0... and F9..., but letters are illegal 
SELECT USERID,HEX(USERID) 
FROM   MY.TABLE_OF_AUTHIDS
WHERE  USERID BETWEEN X'463F000000000000' AND X'4639393939393939' 
OR     USERID BETWEEN X'4C30000000000000' AND X'4C39393939393939' 
; 

-- any userid between Lnnnnn und Fnnnnnnn, but letters are illegal 
SELECT USERID,lcase(substr(USERID,2)) 
FROM   MY.TABLE_OF_AUTHIDS
where  ucase(substr(USERID,2)) = lcase(substr(USERID,2)) 
and    substr(userid,1,1) in ('L','F') 
; 

-- IS_NUM and IS_CHAR user defined functions to verify userid`s
CREATE FUNCTION IS_CHAR (IN_STR VARCHAR(256)) 
RETURNS SMALLINT 
LANGUAGE SQL 
RETURN 
        CASE 
        WHEN TRANSLATE(UPPER(IN_STR),' ','ABCDEFGHIJKLMNOPQRSTUVWXYZ') = ' ' 
        THEN 1 
        ELSE 0 
END; 

CREATE FUNCTION IS_NUM (IN_STR VARCHAR(256)) 
RETURNS SMALLINT 
LANGUAGE SQL 
RETURN 
        CASE 
        WHEN TRANSLATE(IN_STR,' ','01234567890') = ' ' 
        THEN 1 
        ELSE 0 
        END; 
SELECT USERID,IS_NUM(SUBSTR(USERID,2)),IS_CHAR(SUBSTR(USERID,2)) 
FROM   MY.TABLE_OF_AUTHIDS 
WHERE  IS_NUM(SUBSTR(USERID,2)) = 1

In this sample the SQL’s have to check incoming userid’s for having ..

• L0nnnnnn or F0nnnnnn
• where n=0-9
• except first letter should not have any other letter or special character

Any comment? Do not hesitate to provide YOUR solution! Send your suggestions to webmaster@ruban.de!