replace Regular Expression

[pkohli8]

Hi,
I have one table with two columns in one column Names of different Teams are stored and in other columns ....different departments are stored, basically these departments are divided into 3 categories say category 1, category 2, category 3 and are stored in second column in this format Category 1 | category 2 | Category 3 means these 3 categories are differencited with PIPE symbol, so my task is put these categories into 3 different columns.
e.g
TEAMS Departments
ABDteam Finance,Cost Centre | Oranization,Health |HR
XGHteam Finance | Oranization,Health |HR
FJUteam Finance,Cost Centre | Oranization |HR

I have tried similar problem with oracle 10g database using regular expressions but i guess oracle 8i is not compatible with regular expression.

so anyone could help me...

Thanks in Advance

 

[SantaMufasa]

So, pkohli8, how do you want your above sample data to appear as a result of your query? Once we confirm that, then we can produce one or more solutions/alternatives for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Beilstwh]

If I could suggest, never use a single column to store multiple bits of information. You should have setup another table that would link back to the particular team record where the second table had a seperate row for each department.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Bill is absolutely correct. It is typically a very poor database design that requires parsing of discrete information from a single column. (I just wanted to add my "vote" for Bill's excellent advice.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pkohli8]

Hi SantaMufasa,
Thanks for your reply, I know its very poor table design, but the table was created considering other factors...right now I just have to create a report that requires a breakdown of above second column into seprate columns
e.g
TEAMS Departments
ABDteam Finance,Cost Centre | Oranization,Health |HR
XGHteam Finance | Oranization,Health |HR
FJUteam Finance,Cost Centre | Oranization |HR

The final result needs to be

TEAMS column1 column2 column3
ABDteam Finance,Cost Centre Oranization,Health HR
XGHteam Finance Oranization,Health HR
FJUteam Finance,Cost Centre Oranization HR

Thanks
pkohli

 

[SantaMufasa]

Here is a solution for you, based upon consistent behaviour of your delimiters (i.e., " | " for the first delimiter and " |" for the second delimiter):

select * from pko
/

TEAMS      DEPARTMENTS
---------- --------------------------------------------
ABDteam    Finance,Cost Centre | Oranization,Health |HR
XGHteam    Finance | Oranization,Health |HR
FJUteam    Finance,Cost Centre | Oranization |HR

col column1 format a20
col column2 format a20
col column3 format a20
select teams
      ,substr(departments,1,instr(departments,'|')-2)column1
      ,substr(departments,instr(departments,'|')+2
              ,(instr(departments,'|',1,2)-2)
               -instr(departments,'|'))column2
      ,substr(departments,instr(departments,'|',1,2)+1)column3
  from pko;

TEAMS      COLUMN1              COLUMN2              COLUMN3
---------- -------------------- -------------------- -------
ABDteam    Finance,Cost Centre  Oranization,Health   HR
XGHteam    Finance              Oranization,Health   HR
FJUteam    Finance,Cost Centre  Oranization          HR

Let us know if this satisfies your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pkohli8]

Thanks!!!!!!!!!!
It worked Perfectly