Function to parse out numeric value 1

[sasa888]

Hi all, I would like to know if there is a function exists in oracle to perform the following. We have a free-form text field and we need to retrieve a numeric value from within, and the numeric can only be up to 2 character long. The numeric is ususally stored in this format '20 CAT'. Since this is a free-form text field, user can also input '20CAT' or 'XXX20CAT'. What function should I use to achieve what I need? Please help.

 

[SantaMufasa]

So, Sasa, are you always looking for the first numeral in the string (plus the next character if also numeric)? If so, then the following function should do what you want:

create or replace function get_num (str_in varchar2) return number is
 num_loc  number;
 num_hold number;
 non_numeric exception;
 pragma exception_init(non_numeric,-6502);
begin
 num_loc := instr(translate(str_in,'0123456789','^^^^^^^^^'),'^');
 if num_loc = 0 then
  return null;
 end if;
 begin
     num_hold := substr(str_in,num_loc,2);
     return num_hold;
 exception when non_numeric then
     return substr(str_in,num_loc,1);
 end;
end;
/

Function created.

SQL> select get_num('XXX20CAT') from dual;

GET_NUM('XXX20CAT')
-------------------
                 20

1 row selected.

SQL> select get_num('20CAT') from dual;

GET_NUM('20CAT')
----------------
              20

1 row selected.

SQL> select get_num('20 CAT') from dual;

GET_NUM('20CAT')
----------------
              20

1 row selected.

SQL> select get_num('xxx') from dual;

GET_NUM('XXX')
--------------


1 row selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sasa888]

Hi Mufasa, thanks for your response. Do you think it is possible to achieve your solution without creating a function?? Like build the whole logic within the sql itself? Thanks for your help!!

 

[SantaMufasa]

Sasa,

Yes, we can build the code to do what you want in SQL, but that code is a bit of a mess...more code than with the function. Is there are reason you don't want/can't use a function? Or are you just testing our SantaMufasa's SQL-coding skills?

If you come up with a reasonable justification for the SQL-only solution, I'll build you the code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sasa888]

Hi, I am definitely not testing your skill. It is a pain to ask the developer to put that code in place, if I am create it within the sql statement, it will save me some hassel. BTW, my job role is not to create pl-sql function

 

[SantaMufasa]

I understand. Here are some sample data followed by SQL-only code to do what you want along with sample output from the code:

Section 1 -- Sample data:

select * from sasa order by 1;

20 CAT
20CAT
ABC1CAT
ABCDE
CAT 20
XXX20CAT

Section 2 -- Sample code and output:

******************************************************************************************
col val heading "Sample|Values" format a8
col x heading "Extracted|Number" format a9
select val,substr(val,
-- find starting point
    instr(translate(val,'0123456789','^^^^^^^^^^'
                   ),'^'
         ),
-- find length
    decode(instr(
           translate(substr(val,decode(sign(instr(translate(val,'0123456789','^^^^^^^^^^'
                                                           ),'^'
                                                 )
                                           ),
                                         0,length(val),
                                         1,instr(translate(val,'0123456789','^^^^^^^^^^'
                                                          ),'^'
                                                )
                                      )+1,1
                           ),'0123456789','^^^^^^^^^^'
                    ),'^'
                ),
           null,0,
           0,1,
           2
           )
                ) x
from sasa
order by val
/

Sample   Extracted
Values   Number
-------- ---------
20 CAT   20
20CAT    20
ABC1CAT  1
ABCDE
CAT 20   20
XXX20CAT 20

Let us know if this takes care of business for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

So if I understand correctly it's a pain to ask your paid developer to call already created (for free!) function? And it's not a pain to ask somebody to make your own (sql) work? Good business!

Regards, Dima

http://seminihin.narod.ru