get numbers from string delimiter by ',' in oracle sql

Question

I have a varchar collumn which may contain format like this:

123,124,125,126

Now i want to get all number and put it in a single column like this in select command

123
124
125
126

Any idea?

Possible duplicate of http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco — Deepika Janiyani, Nov 11 '13 at 08:49

Dba · Answer 1 · 2013-11-11T09:24:05.577

1

Try this too,

with test as 
(
SELECT '123,124,125,126' str FROM dual  
)  
SELECT regexp_substr (str, '[^,]+', 1, ROWNUM) SPLIT  
FROM   TEST  
CONNECT BY LEVEL <= LENGTH (regexp_replace (str, '[^,]+'))  + 1;

Try this if you have an additional comma at the end,

with test as 
(
SELECT '123,124,125,126,' str FROM dual  
)
SELECT regexp_substr(str,'[^,]+', 1, LEVEL) FROM test
connect by regexp_substr(str, '[^,]+', 1, level) is not null;

edited Nov 11 '13 at 09:24

answered Nov 11 '13 at 08:59

Dba

6,511
1
24
33

Assume the problem `'123,124,125,126,'` == a comma in the end. You return a empty a empty row. – Srini V Nov 11 '13 at 09:16
@realspirituals, Thanks for pointing out, I have added another answer to overcome this like issue. – Dba Nov 11 '13 at 09:26

score 0 · Accepted Answer · answered Nov 11 '13 at 08:53

Answering umpteenth time...

WITH CTE
    AS (SELECT
             '123,124,125,126' AS COL1
        FROM
             DUAL)
SELECT
      REGEXP_SUBSTR ( COL1,
                   '[^,]+',
                   1,
                   RN )
          COL1
FROM
          CTE
      CROSS JOIN
          (SELECT
                ROWNUM RN
           FROM
                (SELECT
                       MAX ( LENGTH ( REGEXP_REPLACE ( COL1,
                                                '[^,]+' ) ) )
                       + 1
                           MAX_L
                 FROM
                       CTE)
           CONNECT BY
                LEVEL <= MAX_L)
WHERE
      REGEXP_SUBSTR ( COL1,
                   '[^,]+',
                   1,
                   RN )
          IS NOT NULL
ORDER BY
      COL1;

score 0 · Answer 3 · answered Oct 26 '18 at 18:27

Alternatively; substr, instr, lag and regexp_count together as :

select substr(str,second,first-second) as "Result String"       
  from
  (
    with t(str) as
    (   
     select '123,124,125,126' from dual
    )
     select replace(instr(str,',',1,level),0,length(str)+1) first,
            nvl(lag(instr(str,',',1,level)) over (order by level),0)+1 second,
            str              
       from dual
       cross join ( select str from t )
     connect by level <= regexp_count(str,',')+1
  );

Rextester Demo

get numbers from string delimiter by ',' in oracle sql

3 Answers3