-2

I have a string which as values as below:

Countries = USA, Canada, Italy

I would like to seperate them and display it as list like below

Countries
USA
Canada
Italy

How can I do this using SQL?

halfer
  • 19,471
  • 17
  • 87
  • 173
Raja Mohan
  • 1
  • 1
  • @a_horse_with_no_name That question is not a duplicate of this one - yes, the initial part is similar but then the OP of that question wants to group and count the items - the accepted answer generates far too many rows to answer this question properly and then uses the trick of getting `COUNT()` to ignore `NULL` values to obtain the answer in that question. Yes, this probably should be closed as a duplicate but not with that target. – MT0 Feb 14 '17 at 12:51
  • An better duplicate (but we can't link to the documentation pages): http://stackoverflow.com/documentation/oracle/1968/splitting-delimited-strings#t=201702141232438006307 – MT0 Feb 14 '17 at 12:53
  • Another duplicate - http://stackoverflow.com/questions/17419160/connect-by-to-generate-rows-from-multiple-delimited-string – MT0 Feb 14 '17 at 12:55
  • @MT0: the dup probably does not matter if no effort has been expended. "Too broad" is fine - we need to get into the habit of encouraging OP effort, and to teach that we're not a crowd-sourced search engine. – halfer Feb 14 '17 at 20:30
  • @halfer I would argue that it does matter - not for the OP of this question (who should have done due diligence before posting) but for the person who finds this question in the future, thinking that they have done due diligence, and spends their time trying to get the linked question to do what they want only to find that it doesn't quite answer this question. – MT0 Feb 15 '17 at 09:10
  • @MT0: ah yes, I agree with that. I meant that since it was too broad, it should be closed as Too Broad instead of Duplicate. – halfer Feb 15 '17 at 10:53

2 Answers2

1

There are multiple solutions posted in the Splitting Delimited Strings topic of the Oracle documentation pages.

One is:

Sample Data:

CREATE TABLE table_name ( id, list ) AS
SELECT 1, 'a,b,c,d' FROM DUAL UNION ALL -- Multiple items in the list
SELECT 2, 'e'       FROM DUAL UNION ALL -- Single item in the list
SELECT 3, NULL      FROM DUAL UNION ALL -- NULL list
SELECT 4, 'f,,g'    FROM DUAL;          -- NULL item in the list

Query:

SELECT t.id,
       v.COLUMN_VALUE AS value,
       ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ROWNUM ) AS lvl
FROM   table_name t,
       TABLE(
         CAST(
           MULTISET(
             SELECT REGEXP_SUBSTR( t.list, '([^,]*)(,|$)', 1, LEVEL, NULL, 1 )
             FROM   DUAL
             CONNECT BY LEVEL < REGEXP_COUNT( t.list, '[^,]*(,|$)' )
           )
           AS SYS.ODCIVARCHAR2LIST
         )
       ) v;

Output:

        ID ITEM           LVL
---------- ------- ----------
         1 a                1 
         1 b                2 
         1 c                3 
         1 d                4 
         2 e                1 
         3 (NULL)           1 
         4 f                1 
         4 (NULL)           2 
         4 g                3
MT0
  • 113,669
  • 10
  • 50
  • 103
0

You can use a regex_substr and a connect by 

  select regexp_substr( 'USA, Canada, Italy','[^,]+', 1, level) from dual
  connect by regexp_substr('USA, Canada, Italy', '[^,]+', 1, level) is not null;
ScaisEdge
  • 129,293
  • 10
  • 87
  • 97
  • but the countries are not static all the time...it may change on user to another.. by the way "- Employee Custom Attributes".TEXT_0 is my colomn title. I want to show whatever the values in this colomn with comma seperator to list. thanks, – Raja Mohan Feb 14 '17 at 12:36
  • This will only work for a single row of data - when there are multiple input rows then the `CONNECT BY` clause will start producing connections across the rows and there will be many, many duplicate rows generated. – MT0 Feb 14 '17 at 12:36