SQL Seperate delimiter and create new row

Question

I have following table content.

COLUMN_A  | COLUMN_B

a         | 123!234!345
b         | 809!123!678!456
c         | 678!789

Is there a way to have a SQL to separate the delimiter "!" and generate a new row as shown below? The number of delimiter in column_B is in random

COLUMN_A  | COLUMN_B

a         | 123
a         | 234
a         | 345
b         | 809
b         | 123
b         | 678
b         | 456
c         | 678
c         | 789

possible duplicate of [Turning a Comma Separated string into individual rows](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) and http://stackoverflow.com/questions/2938427/converting-delimited-string-to-multiple-values-in-mysql — radar, Jan 09 '15 at 02:44
Different databases support very different dialects of SQL-- at a minimum, you'd get three very different answers for Oracle, SQL Server, and MySQL. That makes it overly broad for this site-- you could get three different answers that were all "correct". If you restrict yourself to just one of those databases, the question would be a much better fit for this site. — Justin Cave, Jan 09 '15 at 03:00
ok if that is the case can we have oracle solution since SQL is already available? — Jonathan Chan, Jan 09 '15 at 03:02

score 0 · Answer 1 · answered Jan 09 '15 at 03:34

Ok found the answer for Oracle. Works only on 11g and 12c

select COLUMN_A, subSTR(COLUMN_B, inSTR(COLUMN_B, '!', 1, lvl) + 1, inSTR(COLUMN_B, '!', 1, lvl + 1) - inSTR(COLUMN_B, '!', 1, lvl) - 1) name 
from
( select '!' || COLUMN_B || '!' as COLUMN_B, COLUMN_A from table ),
( select level as lvl from dual connect by level <= 100 )
where lvl <= length(COLUMN_B) - length(replace(COLUMN_B, '!')) - 1
order by COLUMN_A, COLUMN_B

SQL Seperate delimiter and create new row

1 Answers1