Parsing String

[szeiss]

Using Oracle 9i. I have a function such as:

CREATE OR REPLACE FUNCTION TEST_FUNCTION(
p_string VARCHAR2)
RETURN NUMBER

The string being passed could be:

1111 222 3333 or
1111 2222 3333

meaning the second set of numbers (the 2's) could be 3 or 4 characters in length.

I need to parse this string into 3 seperate fields to use in my select statement.

Thanks

 

[taupirho]

Are the groups of numbers delimited by a space character?

 

[szeiss]

Sorry, yes they are

1111<space>222<space>3333 or
1111<space>2222<space>3333

Thanks

 

[BJCooperIT]

Functions only return one value. You have to add a parameter to specify which piece you want.

CREATE OR REPLACE FUNCTION f_get_piece
( 
    p_text          IN  VARCHAR2,
    p_piece         IN  NUMBER
) 
RETURN NUMBER
IS
    v_start_pos     NUMBER      := 1;
    v_last_pos      NUMBER      := NULL;
    v_number        NUMBER      := NULL;
BEGIN
    IF  p_piece > 1 THEN
        v_start_pos := instr(p_text,' ',1,p_piece-1) + 1;
    END IF;
    v_last_pos  := instr(p_text||' ',' ',1,p_piece) - 1;
    v_number    := substr(p_text,v_start_pos,v_last_pos-v_start_pos+1); 
    RETURN(v_number);
    EXCEPTION WHEN OTHERS THEN RETURN(-99);
END f_get_piece;

Then you call the function for each piece:

SQL> SELECT f_get_piece('1111 2222 3333',1) first_piece,
  2         f_get_piece('1111 2222 3333',2) second_piece,
  3         f_get_piece('1111 2222 3333',3) third_piece,
  4         f_get_piece('1111 2222 3333',4) missing_piece
  5  FROM   dual;

FIRST_PIECE SECOND_PIECE THIRD_PIECE MISSING_PIECE
----------- ------------ ----------- -------------
       1111         2222        3333

SQL>

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[taupirho]

A messy, SQL only, alternative

variable x varchar2(50)
exec :x := '111 222 33333'
select substr( :x||' ', 1, instr(:x,' ')-1 ) first,
       substr( :x||'  ', instr( :x||'  ', ' ') +1,
       instr( :x||'  ', ' ', 1, 2 )-instr(:x||'',' ')-1 ) second,
       rtrim(substr( :x||'  ', instr( :x||'  ',' ',1,2)+1),' ') third
 from dual

 

[szeiss]

Taupirho, this runs great in a sql window, but I get errors in my function. I changed the "exec :x := '111 222 33333'" to my parameter p_string

variable x varchar2(50)
exec :x := p_string
select substr( :x||' ', 1, instrx,' ')-1 ) first,
substr( :x||' ', instr( :x||' ', ' ') +1,
instr( :x||' ', ' ', 1, 2 )-instrx||'',' ')-1 ) second,
rtrim(substr( :x||' ', instr( :x||' ',' ',1,2)+1),' ') third
from dual

I get errors:
PLS-00049: bad bind variable 'X'

Thanks

 

[taupirho]

Try removing the first two lines
then change the rest of the :x's to p_string's

 

[szeiss]

Thanks, I got it working with your help.

 

[szeiss]

Thanks to BJCooperIT for his input also.