deconcatinate

[mgl70]

hi,
Is there any function to deconcatinate a string.
My table has a COLUMN VALUE: SCOTT/TIGER/HEAD/TAIL.
I need to extract the string and put into 4 seperate columns. how do we do this. we can concatinate strings(|| or concat function), I donot know the other way.

table1
------

column name
------------
SCOTT/TIGER/HEAD/TAIL

I need to read the column and extract into 4 diff columns.

firstname LASTNAME MIDDLENAME AKA
---------- -------- ---------- ----
SCOTT TIGER HEAD TAIL


Thanks for your help.

 

[Turkbear]

Hi,
Visit the docs for the usage of the substr and Instr functions:

Something like

select substr(COLNAME,1,Instr(COLNAME,'/') -1) as First...


etc.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[mgl70]

Hi,
I canot do like that since my table has more than 12,000 rows and each and every row has diff length strings.

If we can cut at the delimeter(/), it would be better.

Thanks.

 

[Turkbear]

Hi,
That is what that formula does..It cuts everything before the 1st '/', the docs will show you how to get the rest..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[carp]

That is exactly what turkbear just gave you. It finds where the first '/' occurs and returns everything that precedes it. You would have to do a similar operation to get the second chunk, third chunk, etc.

 

[SantaMufasa]

MGL,

I don't want to interfere with your learning opportunity from the excellent advice that both Turkbear and Carp gave you, but since you are not the first person to ask for such a function and since it appeared that there was a disconnect between Turk's/Carp's advice and your perception of their advice, here is the "deconcat" function you wanted:

Section 1 -- "Deconcat" function source code:

create or replace function deconcat
    (str_in varchar2, which number, delim varchar)
    return varchar2
-- str_in: the character string to parse
-- which : the ordinal string piece you want (e.g., Second piece=2)
-- delim : the delimiting character(s) within your parse string
is
    beg_pt             number;
    len                number;
    loc_of_which_delim number;
    prev_delim         number;
begin
    if which = 0 then -- Piece zero is invalid
        return null;
    elsif which = 1 then -- if first piece wanted, and
        if instr(str_in,delim) = 0 then -- if no delimiter
            return str_in; -- then returns entire string
        end if;
        beg_pt := 1;
    elsif which > 1 then -- if > 1st piece wanted
        if instr(str_in,delim,1,which-1) = 0 then -- no delimeter
            return null; -- then, returns null
        end if;
        beg_pt := instr(str_in,delim,1,which-1)+1;
            -- ^^ fixes begin pt.
    end if;
    loc_of_which_delim := instr(str_in,delim,1,which);
        -- ^^ finds position of end delimiter for this piece
    if loc_of_which_delim = 0 then -- no nth delimiter found
        prev_delim := instr(str_in,delim,1,which-1); -- prev delim loc
        if prev_delim = 0 then -- no previous delimiter
            return null; -- then no nth piece, return null
        else
            len := length(str_in)-beg_pt+1;
                -- ^^ finds length of last piece
        end if;
    else
        len :=  ((loc_of_which_delim)-beg_pt);
            -- ^^ finds length of nth piece
    end if;
    return substr(str_in,beg_pt,len);
end;
/

Function created.

Section 2 -- Sample data:

SQL> select * from mgl;

NAMES
-----------------------
SCOTT/TIGER/HEAD/TAIL
CHER
David/L/Hunt
John//Doe
///Mufasa

5 rows selected.

Section 3 -- Sample invocation of "deconcat" function against MGL sample data:

set pagesize 35
col a heading "firstname" format a10
col b heading "MIDDLENAME" format a10
col c heading "LASTNAME" format a10
col d heading "AKA" format a10
select deconcat(names,1,'/') a
      ,deconcat(names,2,'/') b
      ,deconcat(names,3,'/') c
      ,deconcat(names,4,'/') d
from mgl
/

firstname  MIDDLENAME LASTNAME   AKA
---------- ---------- ---------- ----------
SCOTT      TIGER      HEAD       TAIL
CHER
David      L          Hunt
John                  Doe
                                 Mufasa

5 rows 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.