Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: function to get only numbers
You need:
end if;
before:
end loop;
Also, you don't need to declare the variable 'i' ... it's not incorrect but it's a different instance from the variable used in the loop counter.
Another thing you might consider is using:
between '0' and '9'
instead of
in ('0','1','2','3','4','5','6','7','8','9')
Steve Parfitt
Philippe Makowski wrote:
>
> why this function does not compile ?
>
> CREATE OR REPLACE FUNCTION char2num (charnum in varchar2) RETURN number IS
> i number;
> digits number;
> current_digit char(1);
> parsechar varchar2;
> BEGIN
> parsechar :='';
> digits := length(charnum);
> for i in 1..digits loop
> current_digit := SUBSTR(charnum, i, 1);
> if current_digit in ('0','1','2','3','4','5','6','7','8','9') then
> parsechar := parsechar || current_digit;
> end loop;
> return to_number(parsechar);
> END char2num;
Received on Wed Jul 04 2001 - 21:40:29 CDT