Oracle: Nth Record from Comma Separated String

SELECT REGEXP_SUBSTR(MYCOL, '[^,]+', 1, 2) AS VAL
FROM
(
   SELECT 'CAT,DOG,MOUSE,RAT,BAT,CAR,DOOR,LOCK' AS MYCOL FROM DUAL
);

Output would be - DOG

to get 4th value just change
REGEXP_SUBSTR(MYCOL, '[^,]+', 1, 4)


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: Department wise Employee Ids Comma Separated
- Oracle: Comma Separated String To Rows

20 comments:

  1. thanx its really good :)

    ReplyDelete
  2. it's really helpful.
    Some times we need to handle comma separated strings.

    Thanks

    ReplyDelete
    Replies
    1. Dont forget to check related links of this post !!!

      Delete
  3. This is useful. My algortimic function library entry

    FUNCTION EXTRACTELEMENT(p_Txt VARCHAR2, pos_n integer, p_Separator VARCHAR2) RETURN VARCHAR2 AS res varchar2(255);

    will get a more elegant solution based on this.

    Thanks

    ReplyDelete
    Replies
    1. Thanks for appreciation :) keep visiting for more useful articles.

      Delete
  4. How can we get the second last position , putting negative number for the gives an error. Please help.

    ReplyDelete
    Replies
    1. select * from
      (
      SELECT REGEXP_SUBSTR(MYCOL, '[^,]+', 1, level) AS VAL, length(mycol) - length(replace(mycol,',')) + 2 - rownum rn
      FROM
      (
      SELECT 'CAT,DOG,MOUSE,RAT,BAT,CAR,DOOR,LOCK' AS MYCOL FROM DUAL
      )
      connect by level <= length(mycol) - length(replace(mycol,','))+1
      ) where rn=2

      Delete
    2. also
      connect by level <= length(mycol) - length(replace(mycol,','))+1
      can be replaced with
      connect by REGEXP_SUBSTR(MYCOL, '[^,]+', 1, level) is not null

      Delete
    3. select substr(x,instr(x,',',-1,2)+1,instr(x,',',-1,1)-instr(x,',',-1,2)-1)
      from (select '45,44,58,99,99,100,200' x from dual)

      Delete
  5. Hi Nimish,
    Can u suggest that how it is work for nth position.
    Please explain it

    ReplyDelete
    Replies
    1. REGEXP_SUBSTR(MYCOL, '[^,]+', 1, 4)
      [^,]+ Says: Find until comma (,, any number of characters
      1: start from 1st character
      4: occurrence

      Delete
  6. appreciate if you put demo videos of examples.

    ReplyDelete
  7. select regexp_substr(data,'([a-zA-Z]{1,})',1,level) from ( SELECT 'CAT,DOG,MOUSE,RAT,BAT,CAR,DOOR,LOCK' AS data FROM DUAL ) connect by level <=regexp_count(data,',')+1 ;

    REGEXP_SUBSTR(DATA,'([A-ZA-Z]{1,})',1,LEVEL)
    --------------------------------------------------------------------------------
    CAT
    DOG
    MOUSE
    RAT
    BAT
    CAR
    DOOR
    LOCK

    ReplyDelete
  8. select regexp_substr(data,'([a-zA-Z]{1,})',1,level) from ( SELECT 'CAT,DOG,MOUSE,RAT,BAT,CAR,DOOR,LOCK' AS data FROM DUAL ) connect by level <=regexp_count(data,',')+1 ;

    REGEXP_SUBSTR(DATA,'([A-ZA-Z]{1,})',1,LEVEL)
    --------------------------------------------------------------------------------
    CAT
    DOG
    MOUSE
    RAT
    BAT
    CAR
    DOOR
    LOCK

    ReplyDelete
  9. how to get string between from_index and to_index specified ? like index 0 to 3rd
    input data = 'fsfa:c19a:2:66:0:0:0:1e01
    output data = fsfa:c19a:2:66

    ReplyDelete
  10. Perfect, saved my day!

    ReplyDelete
  11. how to sum a delimited column in oracle?

    ReplyDelete
  12. SELECT REGEXP_SUBSTR(MYCOL, '[^,]+', 1, 2) AS VAL
    FROM
    (
    SELECT 'CAT,,MOUSE,RAT,BAT,CAR,DOOR,LOCK' AS MYCOL FROM DUAL
    );

    ReplyDelete
  13. Not returns expected value if we have blank in 2nd position SELECT REGEXP_SUBSTR(MYCOL, '[^,]+', 1, 2) AS VAL
    FROM
    (
    SELECT 'CAT,,MOUSE,RAT,BAT,CAR,DOOR,LOCK' AS MYCOL FROM DUAL
    );

    ReplyDelete