One simple way for single string
with t as
(select '01,05,10,15,20,25,30,35,50,75,23,03,04,00,00,00,00,00' txt from dual)
select regexp_substr ( txt, '[^,]+', 1, level) data from t CONNECT BY level <= length (txt) - length (replace (txt, ',')) + 1
Another Example
Lets say '3625,3071,1312,1113' is stored as a comma separated string,and we want to get the Employeenames from employee table of these ids.
SELECT EMPLOYEENAME FROM
(
SELECT
TRIM(SUBSTR(MYTEXT, INSTR (MYTEXT, ',', 1, level) + 1, INSTR (MYTEXT, ',', 1, level+1) - INSTR (MYTEXT, ',', 1, level) -1 )) AS MYVALUES
FROM
(
SELECT ',' || '3625,3071,1312,1113' ||',' AS MYTEXT FROM dual
)
CONNECT BY
level <= LENGTH(MYTEXT)-LENGTH(REPLACE(MYTEXT,',',''))-1
)A, EMPLOYEE
WHERE MYVALUES=EMPLOYEEID
Related Links:
- Function to Compare two Comma Separated Strings in Oracle
- Oracle: New String Aggregation Techniques
- Oracle: Difference between wm_concat and ListAgg
- Oracle: Comma Separated String To Rows 11g
- Oracle: Comma Separated String To Rows 10g Onwards
- Oracle: Nth Record from Comma Separated String
- Oracle: Department wise Employee Ids Comma Separated
We can also use:
ReplyDeleteselect
regexp_substr (txt, '[^,]+', 1,level) data
from t
CONNECT BY level <= regexp_count(txt,'[,]+')+1
yes, regexp_count(txt,'[,]+')+1 and length (txt) - length (replace (txt, ',')) + 1 both have same result
DeleteThanks Ashish. You saved my day.
Delete