row and column delimiting in oracle sql

Question

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!

Does this answer your question? [How to convert comma separated values to rows in oracle?](https://stackoverflow.com/questions/38371989/how-to-convert-comma-separated-values-to-rows-in-oracle). "Comma" in this particular case is `chr(10)` — astentx, Jan 20 '23 at 07:07

score 3 · Answer 1 · answered Jan 19 '23 at 20:59

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>

Thank you very much! This certainly helped point me in the right direction. I'm trying to apply this to another field as well, one where the codes are more than one character. I figured out the value pattern for "everything before the ;" = '^\w+' but can't seem to figure out the reverse "everything after the ;". the closest I have is '[;].*' but would like it to not include the ; at the beginning of the returned string — d.sweigart, Jan 23 '23 at 15:54
You're welcome. "Everything after" - simply put - is `substr(val, instr(val, ';') + 1)` — Littlefoot, Jan 23 '23 at 18:43

row and column delimiting in oracle sql

1 Answers1