Thursday, October 30, 2008

Oracle function to find whether a string has special characters?

This function expects a varchar input paramter for which it returns "0" if input string has special characters else returns 1.
CREATE OR REPLACE FUNCTION Containssplchrs(argValue IN VARCHAR)
RETURN NUMBER
AS
tempValue VARCHAR(1);
tempActValues VARCHAR(40);
tempRtrnValue NUMBER;
tempLength NUMBER;
BEGIN
tempActValues:=' ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
tempLength:=LENGTH(argValue);
IF(argValue IS NULL) THEN
RETURN 1;
END IF;
FOR I IN 1.. tempLength
LOOP
tempValue:=SUBSTR(UPPER(argValue),i,1);
tempRtrnValue:=INSTR(tempActValues,tempValue);
IF(tempRtrnValue=0) THEN
RETURN 0;
END IF;
END LOOP;
RETURN 1;
RETURN tempLength;
END;
/

No comments: