CODEWITHSUNDEEP
sqlregexoracle-databaseplsqlsplit
Sri
Sri

Reputation: 19

Is there a possibility to split a cell data to multiple columns in Oracle

I have a clob column in a table whose data look like this

Name: ABC
Place: XYZ
Age: 123
Role: Developer
Skill: SQL

There are hundreds of records in table emp, where each emp_id will have a column wit description like above. Now I want to display this data as each column for an emp id. Something like this

select emp_id, name, place, age, role, skill from emp where emp_id=xx;

Is this possible to do using reg exp? or any other way? Please suggest.

Upvotes: 0

Views: 65

Answers (1)

pifor
pifor

Reputation: 7882

Yes it is possible to use REGEXP_SUBSTR on a CLOB column:

OPS$ORACLE@FTEX>desc emp;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 EMP_ID                                                                     NUMBER
 X                                                                          CLOB

OPS$ORACLE@FTEX>select * from emp;

    EMP_ID X
---------- --------------------------------------------------------------------------------
         1 Name: ABC Place: XYZ Age: 123 Role: Developer Skill: SQL

OPS$ORACLE@FTEX>create or replace view v_emp as
  2  (
  3  select emp_id,
  4         regexp_substr(x, '(\S*)(\:)(\s)(\S*)', 1, 1, null, 4) as Name,
  5         regexp_substr(x, '(\S*)(\:)(\s)(\S*)', 1, 2, null, 4) as Place,
  6         regexp_substr(x, '(\S*)(\:)(\s)(\S*)', 1, 3, null, 4) as Age,
  7         regexp_substr(x, '(\S*)(\:)(\s)(\S*)', 1, 4, null, 4) as Role,
  8         regexp_substr(x, '(\S*)(\:)(\s)(\S*)', 1, 5, null, 4) as Skill
  9         from emp
 10  );

View created.

OPS$ORACLE@FTEX>select * from v_emp;

    EMP_ID NAME                 PLACE                AGE   ROLE                 SKILL
---------- -------------------- -------------------- ----- -------------------- --------------------
         1 ABC                  XYZ                  123   Developer            SQL

OPS$ORACLE@FTEX>

Upvotes: 1

Related Questions