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
Labels: , ,

20 comments:

  1. VijayNovember 29, 2010 at 4:18 PM

    thanx its really good :)

    ReplyDelete
  2. UnknownMay 14, 2012 at 12:54 PM

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

    Thanks

    ReplyDelete
    Replies
    1. Nimish GargMay 14, 2012 at 1:08 PM

      Dont forget to check related links of this post !!!

      Delete
  3. Othmar LippunerAugust 21, 2012 at 8:04 PM

    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. Nimish GargAugust 21, 2012 at 9:40 PM

      Thanks for appreciation :) keep visiting for more useful articles.

      Delete
  4. Sajid SiddikiNovember 20, 2012 at 4:34 PM

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

    ReplyDelete
    Replies
    1. Nimish GargNovember 20, 2012 at 5:10 PM

      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. Nimish GargNovember 20, 2012 at 5:23 PM

      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. Nimish GargNovember 20, 2012 at 7:01 PM

      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. UnknownJanuary 16, 2017 at 12:01 PM

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

    ReplyDelete
    Replies
    1. Nimish GargJanuary 16, 2017 at 12:29 PM

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

      Delete
  6. UnknownSeptember 7, 2017 at 12:02 PM

    Very much helpful

    ReplyDelete
  7. VinodJuly 17, 2019 at 1:44 PM

    appreciate if you put demo videos of examples.

    ReplyDelete
  8. yoyoJuly 25, 2019 at 9:39 AM

    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. yoyoJuly 25, 2019 at 9:39 AM

    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
  10. sami ul hudaApril 29, 2020 at 3:27 PM

    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
  11. AnonymousAugust 10, 2021 at 12:39 AM

    Perfect, saved my day!

    ReplyDelete
  12. UnknownJanuary 27, 2022 at 5:58 AM

    how to sum a delimited column in oracle?

    ReplyDelete
  13. AnonymousAugust 18, 2023 at 7:30 PM

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

    ReplyDelete
  14. AnonymousAugust 18, 2023 at 7:30 PM

    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

Subscribe to: Post Comments (Atom)