Re: Gotta Be Simple PL/SQL

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Fri, 30 Apr 2004 20:43:47 GMT
Message-ID: <4092b98e.949862750@localhost>

might be a more elegant way to do this.. wrote this func 4-5 years ago... don't guarantee it'll work.. but..

SQL> select char_to_num ('bill is 35 today') from dual;

CHAR_TO_NUM('BILLIS35TODAY')

---

35

that'll be 500 rupees

FUNCTION CHAR_TO_NUM (p_string IN VARCHAR2) RETURN VARCHAR2
AS

v_string varchar2(200);
v_ascii  number(3);
v_saved_string  varchar2(20);

I integer;
K integer;
BEGIN
v_string:=LTRIM(RTRIM(p_string));
v_saved_string:=NULL;
IF v_string=NULL THEN
  RETURN NULL;
END IF; FOR I IN 0..nvl(length(v_string),0) LOOP

  v_ascii:=ASCII(v_string);                     /*look at the first

letter*/
  IF v_ascii NOT IN (48, 49, 50, 51, 52, 53, 54, 55, 56, 57) THEN /*if not

 a number*/

     dbms_output.put_line('INSIDE IF: '||v_saved_string);
     v_string:=SUBSTR(v_string, 2);
         ELSE
     BEGIN
     v_saved_string:=v_saved_string||SUBSTR(v_string,1,1);
     v_string:=SUBSTR(v_string, 2);
     dbms_output.put_line('INSIDE ELSE: '||v_saved_string);
     END;

  END IF;
END LOOP; v_saved_string:=LTRIM(RTRIM(v_saved_string)); IF v_saved_string=NULL THEN
  RETURN NULL;
END IF; RETURN v_saved_string;
END CHAR_TO_NUM; On Fri, 30 Apr 2004 14:42:22 -0500, "Joe" <heltibrand_at_NO_SPAMkarpel.com> wrote:

>Hi all
>
> OK, I feel embarresed.
>
> What is the simplest way to pick a number out of a string, i.e. 'Bill is
>35 today' should return 35. I'm thinking some combination of substr and
>instr.
>
> Any thoughts? Any nice thoughts, at least?
>
>Joe
>
>

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Fri Apr 30 2004 - 15:43:47 CDT