CODEWITHSUNDEEP
sqloracle-databasesplitsubstr
Ralph
Ralph

Reputation: 11

Oracle - text parsing

Can someone help me with parsing substrings for a string column in an Oracle database?

I have 2 columns which I need to take from database, for example:

ID     STRING
425    22 23
416    1 22
682    1 22
688    11 133
703    22 12
707    1
710    1 22 12
715    1
716    1 12
796    333
740    1 22 12

I need to extract every substring devided by a 'space', connect it with the right ID and put it into a new table, for example:

enter code here
ID:    SUBSTRING
425    22
425    23
416    1
416    22
425    22
425    23

I need to assume that I do not know quantity of substrings in STRING column.

I have tried something like this, but it duplicates the records for output:

select ID,
regexp_substr(string,'[^ ]+',1,level),
from (select ID, STRING from TABLE   
where regexp_substr(STRING,'[^ ]+',1,level) is not null
CONNECT BY ID = ID 
and regexp_substr(STRING,'[^ ]+',1,level) is not null

Upvotes: 1

Views: 302

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

you can either use DISTINCT or use SYS_GUID() approach.

SELECT ID
    ,REGEXP_SUBSTR(string, '[^ ]+', 1, LEVEL) AS substring
FROM TABLE1 CONNECT BY LEVEL <= REGEXP_COUNT(STRING, '[^ ]+')
    AND PRIOR ID = ID
    AND PRIOR SYS_GUID() IS NOT NULL

Read this article as to how it works : https://community.oracle.com/thread/2526535

Fiddle demo

Upvotes: 0

Mikhail Kazantsev
Mikhail Kazantsev

Reputation: 31

One of the methods:

with test_table as (
  select 425 id, '22 23' str from dual union all
  select 416   , '1 22'      from dual union all
  select 682   , '1 22'      from dual union all
  select 688   , '11 133'    from dual union all
  select 703   , '22 12'     from dual union all
  select 707   , '1'         from dual union all
  select 710   , '1 22 12'   from dual union all
  select 715   , '1'         from dual union all
  select 716   , '1 12'      from dual union all
  select 796   , '333'       from dual union all
  select 740   , '1 22 12'   from dual ),
xml_table as (
  select id, 
         xmltype('<main><str>' || regexp_replace(str, ' +', '</str><str>') || '</str></main>') xml
    from test_table)
select id,
       extractValue(value(t),'str') result
from xml_table s,
     table(XMLSequence(s.xml.extract('main/str'))) t;
;

Upvotes: 1

Related Questions