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 stackoverflow.com/questions/314824/…Deepika Janiyani– Deepika Janiyani2013-11-11 08:49:10 +00:00Commented Nov 11, 2013 at 8:49
Add a comment
|
3 Answers
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;
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;
Comments
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
);