EXPERT RESPONSE
In order to check for characters in the customer number field, you will have to parse the customer number field for characters. As you probably have already found out, Oracle does not have a function that parses a field in 9i. I have created a function called CheckForNumbers (see below). This is something that a lot of shops have or should have. This function accepts a character string and returns a character string delimited by ",". If there are no characters, the function returns the phrase "No Characters". If there are characters, the function returns the characters that are in the string. This function loops thru the string that's passed in and checks to see if the values are between '0' and '9', as follows:
IF to_char(SUBSTR(p_string,i,1)) between '0' and '9' THEN
Notice the single quotes around the 0 and 9. Without the quotes, it would try to compare characters to numbers. This will then generate an error. I hope this solves your problem.
CREATE OR REPLACE function CheckForNumbers (p_string varchar2)
RETURN varchar2 IS
v_string_len NUMBER;
v_string VARCHAR2(2000);
BEGIN
v_string_len := LENGTH(p_string);
FOR i IN 1..v_string_len LOOP
IF to_char(SUBSTR(p_string,i,1)) between '0' and '9' THEN
null;
else
v_string := v_string||SUBSTR(p_string, i, 1)||',';
end if;
END LOOP;
if v_string is null then
return ('NO Characters');
else
RETURN rtrim(v_string,',');
end if;
END CheckForNumbers;
|
