Split function in oracle to comma separated values with automatic sequence

Question

Need Split function which will take two parameters, string to split and delimiter to split the string and return a table with columns Id and Data.And how to call Split function which will return a table with columns Id and Data. Id column will contain sequence and data column will contain data of the string. Eg.

SELECT*FROM Split('A,B,C,D',',')

Result Should be in below format:

|Id | Data
 --   ----
|1  | A  |
|2  | B  |
|3  | C  |
|4  | D  |

Above link fixed, please see [**Split single comma delimited string into rows in Oracle**](https://lalitkumarb.wordpress.com/2014/12/02/split-comma-delimited-string-into-rows-in-oracle/) — Lalit Kumar B, Aug 13 '17 at 08:06

score 30 · Answer 1 · answered Feb 23 '15 at 15:30

Here is how you could create such a table:

 SELECT LEVEL AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data
   FROM dual
CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;

With a little bit of tweaking (i.e., replacing the , in [^,] with a variable) you could write such a function to return a table.

Lalit Kumar B · Answer 2 · 2017-08-13T07:58:49.687

There are multiple options. See Split single comma delimited string into rows in Oracle

You just need to add LEVEL in the select list as a column, to get the sequence number to each row returned. Or, ROWNUM would also suffice.

Using any of the below SQLs, you could include them into a FUNCTION.

INSTR in CONNECT BY clause:

SQL> WITH DATA AS
  2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3    )
  4  SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  5  FROM DATA
  6  CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0
  7  /

STR
----------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

REGEXP_SUBSTR in CONNECT BY clause:

SQL> WITH DATA AS
  2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3    )
  4  SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  5  FROM DATA
  6  CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL
  7  /

STR
----------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

REGEXP_COUNT in CONNECT BY clause:

SQL> WITH DATA AS
  2        ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3        )
  4      SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  5      FROM DATA
  6      CONNECT BY LEVEL

Using XMLTABLE

SQL> WITH DATA AS
  2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3    )
  4  SELECT trim(COLUMN_VALUE) str
  5    FROM DATA, xmltable(('"' || REPLACE(str, ',', '","') || '"'))
  6  /
STR
------------------------------------------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

Using MODEL clause:

SQL> WITH t AS
  2  (
  3         SELECT 'word1, word2, word3, word4, word5, word6' str
  4         FROM   dual ) ,
  5  model_param AS
  6  (
  7         SELECT str AS orig_str ,
  8                ','
  9                       || str
 10                       || ','                                 AS mod_str ,
 11                1                                             AS start_pos ,
 12                Length(str)                                   AS end_pos ,
 13                (Length(str) - Length(Replace(str, ','))) + 1 AS element_count ,
 14                0                                             AS element_no ,
 15                ROWNUM                                        AS rn
 16         FROM   t )
 17  SELECT   trim(Substr(mod_str, start_pos, end_pos-start_pos)) str
 18  FROM     (
 19                  SELECT *
 20                  FROM   model_param MODEL PARTITION BY (rn, orig_str, mod_str)
 21                  DIMENSION BY (element_no)
 22                  MEASURES (start_pos, end_pos, element_count)
 23                  RULES ITERATE (2000)
 24                  UNTIL (ITERATION_NUMBER+1 = element_count[0])
 25                  ( start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, cv(element_no)) + 1,
 26                  end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, cv(element_no) + 1) ) )
 27  WHERE    element_no != 0
 28  ORDER BY mod_str ,
 29           element_no
 30  /

STR
------------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

You could also use DBMS_UTILITY package provided by Oracle. It provides various utility subprograms. One such useful utility is COMMA_TO_TABLE procedure, which converts a comma-delimited list of names into a PL/SQL table of names.

Read DBMS_UTILITY.COMMA_TO_TABLE

score 7 · Answer 3 · answered Feb 24 '16 at 12:36

Oracle Setup:

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
AS
  p_result       SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

Query

SELECT ROWNUM AS ID,
       COLUMN_VALUE AS Data
FROM   TABLE( split_String( 'A,B,C,D' ) );

Output:

ID DATA
-- ----
 1 A
 2 B
 3 C
 4 D

score 4 · Answer 4 · edited May 23 '17 at 11:54

If you need a function try this.
First we'll create a type:

CREATE OR REPLACE TYPE T_TABLE IS OBJECT
(
    Field1 int
    , Field2 VARCHAR(25)
);
CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
/

Then we'll create the function:

CREATE OR REPLACE FUNCTION TEST_RETURN_TABLE
RETURN T_TABLE_COLL
    IS
      l_res_coll T_TABLE_COLL;
      l_index number;
    BEGIN
      l_res_coll := T_TABLE_COLL();
      FOR i IN (
        WITH TAB AS
          (SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
          UNION
          SELECT '1002' ID, 'D,E,F' STR FROM DUAL
          UNION
          SELECT '1003' ID, 'C,E,G' STR FROM DUAL
          )
        SELECT id,
          SUBSTR(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
        FROM
          ( SELECT ',' || STR || ',' AS STR, id FROM TAB
          ),
          ( SELECT level AS lvl FROM dual CONNECT BY level <= 100
          )
        WHERE lvl <= LENGTH(STR) - LENGTH(REPLACE(STR, ',')) - 1
        ORDER BY ID, NAME)
      LOOP
        IF i.ID = 1001 THEN
          l_res_coll.extend;
          l_index := l_res_coll.count;
          l_res_coll(l_index):= T_TABLE(i.ID, i.name);
        END IF;
      END LOOP;
      RETURN l_res_coll;
    END;
    /

Now we can select from it:

select * from table(TEST_RETURN_TABLE());

Output:

SQL> select * from table(TEST_RETURN_TABLE());

    FIELD1 FIELD2
---------- -------------------------
      1001 A
      1001 B
      1001 C
      1001 D
      1001 E
      1001 F

6 rows selected.

Obviously you'd need to replace the WITH TAB AS... bit with where you would be getting your actual data from. Credit Credit

and both of ur type and function showing compilation error – AKBAR ALI Feb 24 '15 at 04:43 — AKBAR ALI, Feb 24 '15 at 04:43

Alexander Mchedlishvili · Answer 5 · 2016-10-03T12:31:18.057

Use this 'Split' function:

CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sys_refcursor is
v_res sys_refcursor;

begin
  open v_res for 
  WITH TAB AS 
  (SELECT p_str STR FROM DUAL)
  select substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name 
  from
    ( select ',' || STR || ',' as STR from TAB ),
    ( select level as lvl from dual connect by level <= 100 )
    where lvl <= length(STR) - length(replace(STR, ',')) - 1;

     return v_res;
   end;

You can't use this function in select statement like you described in question, but I hope you will find it still useful.

EDIT: Here are steps you need to do. 1. Create Object: create or replace type empy_type as object(value varchar2(512)) 2. Create Type: create or replace type t_empty_type as table of empy_type 3. Create Function:

CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sms.t_empty_type is
v_emptype t_empty_type := t_empty_type();
v_cnt     number := 0;
v_res sys_refcursor;
v_value nvarchar2(128);
begin
  open v_res for
  WITH TAB AS
  (SELECT p_str STR FROM DUAL)
  select substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl +     1) - instr(STR, ',', 1, lvl) - 1) name
  from
    ( select ',' || STR || ',' as STR from TAB ),
    ( select level as lvl from dual connect by level <= 100 )
    where lvl <= length(STR) - length(replace(STR, ',')) - 1;


  loop
     fetch v_res into v_value;
      exit when v_res%NOTFOUND;
      v_emptype.extend;
      v_cnt := v_cnt + 1;
     v_emptype(v_cnt) := empty_type(v_value);
    end loop;
    close v_res;

    return v_emptype;
end;

Then just call like this:

SELECT * FROM (TABLE(split('a,b,c,d,g')))

score 1 · Answer 6 · answered Jun 28 '17 at 04:41

This function returns the nth part of input string MYSTRING. Second input parameter is separator ie., SEPARATOR_OF_SUBSTR and the third parameter is Nth Part which is required.

Note: MYSTRING should end with the separator.

create or replace FUNCTION PK_GET_NTH_PART(MYSTRING VARCHAR2,SEPARATOR_OF_SUBSTR VARCHAR2,NTH_PART NUMBER)
RETURN VARCHAR2
IS
NTH_SUBSTR VARCHAR2(500);
POS1 NUMBER(4);
POS2 NUMBER(4);
BEGIN
IF NTH_PART=1 THEN
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, 1)  INTO POS1 FROM DUAL; 
SELECT SUBSTR(MYSTRING,0,POS1-1) INTO NTH_SUBSTR FROM DUAL;
ELSE
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART-1) INTO  POS1 FROM DUAL; 
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART)  INTO POS2 FROM DUAL; 
SELECT SUBSTR(MYSTRING,POS1+1,(POS2-POS1-1)) INTO NTH_SUBSTR FROM DUAL;
END IF;
RETURN NTH_SUBSTR;
END;

Hope this helps some body, you can use this function like this in a loop to get all the values separated:

SELECT REGEXP_COUNT(MYSTRING, '~', 1, 'i') INTO NO_OF_RECORDS FROM DUAL;
WHILE NO_OF_RECORDS>0
LOOP
    PK_RECORD    :=PK_GET_NTH_PART(MYSTRING,'~',NO_OF_RECORDS);
    -- do some thing
    NO_OF_RECORDS  :=NO_OF_RECORDS-1;
END LOOP;

Here NO_OF_RECORDS,PK_RECORD are temp variables.

Hope this helps.

score 0 · Answer 7 · edited Dec 09 '19 at 07:23

0

Best Query For comma separated in This Query we Convert Rows To Column ...

SELECT listagg(BL_PRODUCT_DESC, ', ') within
   group(   order by BL_PRODUCT_DESC) PROD
  FROM GET_PRODUCT
--  WHERE BL_PRODUCT_DESC LIKE ('%WASH%')
  WHERE Get_Product_Type_Id = 6000000000007

edited Dec 09 '19 at 07:23

MartenCatcher

2,713
8
26
39

answered Dec 09 '19 at 06:35

Muhammad Tazeem

1

score 0 · Answer 8 · answered Aug 29 '21 at 12:58

Created PL/SQL function that can split string by specified delimiter and return result as VARRAY.

CREATE OR REPLACE FUNCTION split(p_parameters VARCHAR2, p_delimiter VARCHAR2) RETURN string_varray AS
    v_delimiter_position NUMBER := 0;
    v_read_position NUMBER :=1;
    v_list string_varray := string_varray();
    v_substring VARCHAR2(4000);
    
    FUNCTION normalize(v_substring VARCHAR2, p_delimiter VARCHAR2) RETURN VARCHAR2 AS
    BEGIN
        RETURN trim(TRAILING p_delimiter FROM trim(BOTH ' ' FROM v_substring));
    END normalize;
BEGIN
    LOOP
         v_delimiter_position := instr(p_parameters, p_delimiter, v_read_position);
         IF v_delimiter_position = 0 THEN
            v_delimiter_position := LENGTH(p_parameters);
         END IF;
         v_substring := substr(p_parameters, v_read_position, v_delimiter_position-v_read_position+1);
         v_list.EXTEND;
         v_list(v_list.LAST) := normalize(v_substring, p_delimiter);
         v_read_position := v_delimiter_position+1;
         IF v_delimiter_position = LENGTH(p_parameters) THEN
            EXIT;
         END IF; 
    END LOOP;  

    RETURN v_list;
END split;

string_varray is VARRAY of VARCHAR2(4000) type. Function also removes whitespaces and the start and end of each value. Invocation example:

select * from table(split('zaa, dddd,ccc', ','));

Will produce three rows in output: zaa dddd ccc

score 0 · Answer 9 · answered Sep 23 '22 at 19:57

0

begin for rec in (select * from table(split('shfgjsdfg,242535', ','))) loop dbms_output.put_line(rec.COLUMN_VALUE);

end loop; end;

-- Output shfgjsdfg 242535

answered Sep 23 '22 at 19:57

Avishek Mallik

21
1
4

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 29 '22 at 02:17

score -4 · Answer 10 · edited May 25 '16 at 16:06

-4

Try like below

select 
    split.field(column_name,1,',','"') name1,
    split.field(column_name,2,',','"') name2
from table_name

edited May 25 '16 at 16:06

techspider

3,370
13
37
61

answered May 25 '16 at 15:05

Ifthakharul Islam Dollar

1

1

This is Oracle, not Sql Server. See the tags. – Gary_W May 25 '16 at 15:34

Split function in oracle to comma separated values with automatic sequence

10 Answers10

Linked

Related