-2
Name    Flag
Aman    A,B
Rajesh  C,D


o/p--> Aman  A
       Aman  B
       Rajesh  C
       Rajesh  D


select name,substr(replace(FLAG,',',null),1,level) from w1
where connect by level <= length(replace(flag,',', null)',');
tomerpacific
  • 4,704
  • 13
  • 34
  • 52
Mahesh Pande
  • 9
  • 2
    You seem to be mixing up several approaches, and possibly confusing regexp_replace with regular replace. You might find an approach that works for you [here](https://stackoverflow.com/q/38371989/266304), but there are many other questions about splitting delimited columns into multiple rows. – Alex Poole Jan 23 '23 at 14:47
  • You have a non-normalized table, which makes it difficult to query. I hope you are using the query you are after in order to create a better table from the bad one. Good luck with this. Littlefoot's answer looks promising. – Thorsten Kettner Jan 23 '23 at 19:15

2 Answers2

1

One option, with sample data

SQL> with test (name, flag) as
  2    (select 'Aman'  , 'A,B'   from dual union all
  3     select 'Rajesh', 'C,D,E' from dual
  4    )

is to split flag at the comma character, up to max number of elements split by comma:

  5  select name,
  6    regexp_substr(flag, '[^,]+', 1, column_value) flag
  7  from test cross join
  8    table(cast(multiset(select level from dual
  9                        connect by level <= regexp_count(flag, ',') + 1
 10                       ) as sys.odcinumberlist))
 11  order by name, flag;

NAME   FLAG
------ --------------------
Aman   A
Aman   B
Rajesh C
Rajesh D
Rajesh E

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

Does this help you?

WITH
    tbl (A_NAME, FLAGS) AS
        (
            Select 'Aman', 'A,B' From Dual Union All
            Select 'Rajesh', 'C,D' From Dual
        )
Select A_NAME, SubStr(FLAGS, 1, InStr(FLAGS, ',', 1, 1) - 1) "FLAG" From tbl Union All
Select A_NAME, SubStr(FLAGS, InStr(FLAGS, ',', 1, 1) + 1) "FLAG" From tbl 
Order By A_NAME, FLAG

A_NAME FLAG
------ ----
Aman   A    
Aman   B    
Rajesh C    
Rajesh D
d r
  • 3,848
  • 2
  • 4
  • 15
  • But of course it doesn't work properly, @Mahesh. It presumes that you'd actually write as many UNIONs as there are rows in a table, which is ... well, *wrong*. Besides, if flag in source table contained more than 2 elements, it would return wrong result entirely. Try flag e.g. 'C,D,E' and see what happens. – Littlefoot Jan 23 '23 at 19:18
  • I *know* dr *knows better*, I've seen their previous answers. This one was written as if cigla fell on their head. Just kidding, dr. – Littlefoot Jan 23 '23 at 19:21