CODEWITHSUNDEEP
sqloracle-database
d.sweigart
d.sweigart

Reputation: 13

row and column delimiting in oracle sql

I'm trying to create a sub-table to reference within a query; the data I need is a separate title and code within a CLOB field like this. Code and title are separated by the semicolon, and each pair is separated by a carriage return (represented by ~).

category values
offender S;Student~T;Teacher~O;Other
reporter S;Student~T;Teacher~O;Other

I'd like to pull a table like this:

category code title
offender S Student
offender T Teacher
offender O Other
reporter S Student
reporter T Teacher
reporter O Other

I'm trying to wrap my head around how to use regexp_substr and can't seem to get it to work, it tells me the command isn't ended properly. The table name is "gen":

select 
    regexp_substr(replace(replace(replace(gen.values,CHR(10),'~'),CHR(13),'~'),';Please Select;*~~',''),'[^~]+',1,LEVEL) as code_title
from dual
CONNECT BY regexp_substr(code_title,'[^~]+',1,LEVEL) IS NOT NULL 
where category = 'Offender';

Any advice (or other best practices for streamlining my query) is very much appreciated!

Upvotes: 0

Views: 50

Answers (1)

Littlefoot
Littlefoot

Reputation: 142958

Table description:

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CATEGORY                                           VARCHAR2(20)
 CVALUES                                            CLOB

Table contents:

SQL> select * from test;

CATEGORY             CVALUES
-------------------- ----------------------------------------
offender             S;Student
                     T;Teacher
                     O;Other

reporter             S;Student
                     T;Teacher
                     O;Other

Query:

SQL> with temp as
  2    (select
  3       category,
  4       regexp_substr(cvalues, '[^' ||chr(10) ||']+', 1, column_value) val
  5     from test cross join
  6       table(cast(multiset(select level from dual
  7                           connect by level <= regexp_count(cvalues, chr(10)) + 1
  8                          ) as sys.odcinumberlist))
  9    )
 10  select category,
 11    regexp_substr(val, '^\w') code,
 12    regexp_substr(val, '\w+$') title
 13  from temp;

CATEGORY             CODE       TITLE
-------------------- ---------- --------------------
offender             S          Student
offender             T          Teacher
offender             O          Other
reporter             S          Student
reporter             T          Teacher
reporter             O          Other

6 rows selected.

SQL>

Upvotes: 3

Related Questions