CODEWITHSUNDEEP
sqloracle-database
Carlo Cruz
Carlo Cruz

Reputation: 41

Comma delimeted using sql query

I have a attributes in a table that have a comma. ex. 123-456,654-321,098-765. Then they are in the same data with the value of 'numbers'. I am using pl/sql oracle application

Output Query
Number | 123-456,654-321,098-765

Assumed Query
Number | 123-456
Number | 654-321
Number | 098-765

Upvotes: 0

Views: 52

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

Sample example how to parse CSV:

SqlFiddleDemo

with temp as
(
    select 'Number' Name , '123-456,654-321,098-765' AS val  from dual
)
select distinct
  t.name, 
  trim(regexp_substr(t.val, '[^,]+', 1, levels.column_value))  as val
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (
        regexp_replace(t.val, '[^,]+'))  + 1) as sys.OdciNumberList)) levels
order by name;

Change the temp CTE with your query which gave you linear output.

Upvotes: 1

Related Questions