convert value from rows into column - OracleDB

Question

I would like to convert values from column TEXT

ID      TEXT
182234  ZO-Z-000-4OB-566WD1#ZO-Z-000-4OB-566WD#ZO-Z-000-4OB-566WD2#
182245  ZO-Z-000-4OB-567WD1#ZO-Z-000-4OB-567WD#

into separate rows (something like this, # will be delimiter)

    ID  TEXT
182234  ZO-Z-000-4OB-566WD1#
182234  ZO-Z-000-4OB-566WD#
182234  ZO-Z-000-4OB-566WD2#
182245  ZO-Z-000-4OB-567WD1#
182245  ZO-Z-000-4OB-567WD#

Oracle Database 18c

Please show us what you have tried so far – sina Sep 17 '20 at 15:34 — sina, Sep 17 '20 at 15:34

score 2 · Answer 1 · answered Sep 17 '20 at 10:05

2

You can use Hierarchical Query along with REGEXP_SUBSTR() function :

SELECT id, REGEXP_SUBSTR(text,'[^#]+',1,level)||'#' AS text
  FROM t
 CONNECT BY level <= REGEXP_COUNT(text,'#')
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR id = id;

ID      TEXT
------  ---------------------
182234  ZO-Z-000-4OB-566WD1#
182234  ZO-Z-000-4OB-566WD#
182234  ZO-Z-000-4OB-566WD2#
182245  ZO-Z-000-4OB-567WD1#
182245  ZO-Z-000-4OB-567WD#

Demo

answered Sep 17 '20 at 10:05

Barbaros Özhan

47,993
9
26
51

Everything works. Thanks for help! – Adam Kasprzak Sep 17 '20 at 10:56
1

you're welcome @AdamKasprzak you can kindly accept as answering the question. – Barbaros Özhan Sep 17 '20 at 11:06

score 0 · Answer 2 · answered Sep 17 '20 at 11:22

0

Wait, if you are on 18c. The below is how you solve it:

select * from t,
lateral
(
    select REGEXP_SUBSTR(text,'[^#]+',1,level)||'#' as new_text
    from dual 
    connect by REGEXP_SUBSTR(text,'[^#]+',1,level) is not null
)

answered Sep 17 '20 at 11:22

Ranagal

329
1
4

convert value from rows into column - OracleDB

2 Answers2