SQL playing with user id’s
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!
Comments
Comments are closed.