SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Return value by pipe (|) delimiter using sql

shuvroApr 21 2020 — edited Apr 21 2020

Hi everyone.

I am using Oracle DB 12c.

I have a master (Lookup) table just like this :

CODE_MASTER

CODE	DESC
A	BEFTN
B	ACC
C	CHECK

and in my transaction table, the column(s) will appear like this :

TRAN_DTL

TRAN	TRAN_CODE
T001	A\|B
T002	A\|B\|C
T003	B\|C
T004	A

Note1:TRAN_CODE column is separated by Pipe (|) delimiter

Note2 : I know, this is not a relational db design. But i got the table this way.

Please help me to build a sql using regular expression or db-function, whatever it is. The output should be :

TRAN	TRAN_CODE	TRAN_DESC
T001	A\|B	BEFTN\|ACC
T002	A\|B\|C	BEFTN\|ACC\|CHECK
T003	B\|C	ACC\|CHECK
T004	A	BEFTN

Thanks in advance.

Added on Apr 21 2020

8 comments

4,049 views