0

I have a table with a column whose values look like these

AAAA\\BBB\CCC

\XXX\\\YYYY\ZZ\

The delimiter '\' can occur one or more number of times between the character strings. The ask is to isolate the character strings in the columns and store them like below

AAAA

BBB

CCC

Please suggest the solution in DB2

user2989216
  • 1
  • Please, explain the meaning of "to isolate the character strings in the columns" in more detail. Do you need to get 1 row with X columns or X rows with a single column from each such a value of X tokens? If you need the former, than what if different values have different number of tokens? – Mark Barinstein Apr 28 '21 at 20:28
  • Please share with us what you have investigated and which SQL you tried. See https://stackoverflow.com/help/how-to-ask – data_henrik Apr 29 '21 at 06:45

1 Answers1

0

Does this help?

with data (test,str) as (values            
(1, 'AAAA\\BBB\CCC'),                      
(2, '\XXX\\\YYYY\ZZ\')                     
)                                          
select regexp_substr(str,'[A-Z]+', 1, n)   
from   data                                
cross  join (values (1),(2),(3),(4)) x (n) 
where  n <= regexp_count(str,'[A-Z]+')     
order  by test,n
user2398621
  • 86
  • 1
  • 3