Oracle: Comma Separated String To Rows


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

3 comments:

  1. We can also use:

    select
    regexp_substr (txt, '[^,]+', 1,level) data
    from t
    CONNECT BY level <= regexp_count(txt,'[,]+')+1

    ReplyDelete
    Replies
    1. yes, regexp_count(txt,'[,]+')+1 and length (txt) - length (replace (txt, ',')) + 1 both have same result

      Delete
    2. Thanks Ashish. You saved my day.

      Delete