SQL Tutorials

How to Split comma separated string in to rows?

Amit S August 2, 2019

In my previous articles I have given multiple examples of regular expressions in detail. In this article I would like to give you explanation about How to Split comma separated string in to rows?. I will explain Split of comma separated string in to rows with multiple examples in detail. In multiple industry scenarios you need to work on comma separated vales and you need to split comma separated values and put it in row.

Split Comma separated Values in SQL Server :

It is very easy to split the comma separated value in SQL server than the Oracle. SQL server has provided on in built function which will directly convert the comma separated string in to rows format. The function name is String_Split().In this section i would like to give you the examples of SQL server to split comma separated values.

Syntax :

STRING_SPLIT(String_name,’Any Seperator’);

I would like to give you one example for String_Split() function so that you will get clear idea about how to convert the value to rows.

SELECT value FROM STRING_SPLIT(‘Amit,Rahul,Bikesh,Sid’, ‘,’);

The above query will split the values in to the rows.

Split Comma separated String in Oracle :

In this section i would like to give you explanation about splitting the string in Oracle.I have already explained about REGEXP function with multiple examples in detail.To split comma separated values user needs to use REGEXP_SUBSTR function.

Example:

with Split_values as

(select ‘Sandeep,Rahul,Prathusha,Nirav’ str from dual

)

select regexp_substr (str, ‘[^,]+’, 1, rownum) Values_Split

from Split_values

connect by level <= length (regexp_replace (str, ‘[^,]+’)) + 1;

In above query we have used regexp_substr and regexp_replace function to convert the value in to rows to column.

Output :

Values_split

Sandeep

Rahul

Prathusha

Nirav

Split comma separated String in Oracle complex example

  • Split comma separated string

I have explained simple example of Splitting comma separated value in Oracle.In this section i would like to give you complex example which will be useful for PL SQL scenarios as well.

Question : Suppose there are different columns which contains comma separated values how to convert it in single row.

with Multiple_Column_Example as
(
select 1 id, ‘Ramu,Sonal,Sima’ Cust_Name from dual union all
select 5 id, ‘Rakesh,Pintoo’ Cust_Name from dual union all
select 8 id, ‘Deepti’ Cust_Name from dual
)
select

regexp_substr (str, ‘[^,]+’, 1, rn) Split_Values
from Multiple_Column_Example
cross
join (select rownum row_num
from (select max (length (regexp_replace (str, ‘[^,]+’))) + 1 Max_val
from Multiple_Column_Example
)
connect by level <= Max_val
)
where regexp_substr (str, ‘[^,]+’, 1, row_num) is not null
order by id;

Output :

Split_Values

Ramu

Sonal

Sima

Rakesh

Pintoo

Deepti

This example  will help to split multiple comma separated values. I hope this article helps you.If you like this article or if you have any issues with the same kindly comment in comments sections.

8 Replies to “How to Split comma separated string in to rows?”

  1. Hello, after a couple trivial corrections, it ran fine.

    with Multiple_Column_Example as
    ( select 1 id, ‘Ramu,Sonal,Sima’ str from dual union all
    select 5 id, ‘Rakesh,Pintoo’ str from dual union all
    select 8 id, ‘Deepti’ str from dual)

    select regexp_substr (str, ‘[^,]+’, 1, row_num) Split_Values
    from Multiple_Column_Example
    cross join (select rownum row_num
    from (select max (length (regexp_replace (str, ‘[^,]+’))) + 1 Max_val
    from Multiple_Column_Example
    )
    connect by level <= Max_val
    )
    where regexp_substr (str, '[^,]+', 1, row_num) is not null
    order by id;

  2. USE the FIXED QUERY as below:

    with Split_values as
    (select 'Sandeep,Rahul,Prathusha,Nirav' str from dual
    )
    select regexp_substr (str, '[^,]+', 1, rownum) Values_Split
    from Split_values
    connect by level <= length (regexp_replace (str, '[^,]+')) + 1;

  3. Hi,
    I need a value on sort order on basis of comma separated values, I mean ‘Sandeep,Rahul,Prathusha,Nirav’ from this String i need to get Sandeep becuase sandeep is the highest.

  4. 1. Create your own table type.
    create or replace TYPE STRING_TBL as table Of VARCHAR2(32767);

    2. Create your own SPLIT_STRING function
    create or replace FUNCTION split_string (
    i_string VARCHAR2,
    i_delimiter VARCHAR2
    ) RETURN string_tbl PIPELINED
    AS
    /*
    Accepts delimited string and returns it as a result set that can be selected in a query.
    If the delimiter isn’t found, the given input string will be returned as a single row.
    */

    — Variables
    v_delimiter_index PLS_INTEGER;
    v_string VARCHAR2(4000) := i_string;
    BEGIN
    LOOP
    — Get the position of next delimiter.
    v_delimiter_index := INSTR(v_string, i_delimiter);

    — If the delimiter is found, extract the preceding value from it, pipe the value as output,
    — and remove that instance of the delimiter and preceding string from the given string.
    IF v_delimiter_index > 0 THEN
    — Send the string value as output.
    PIPE ROW(SUBSTR(v_string, 1, v_delimiter_index – 1));

    — Remove this instance of the delimiter and string value from the given string.
    v_string := SUBSTR(v_string, v_delimiter_index + LENGTH(i_delimiter));
    ELSE
    — If the delimiter is not found, just return what’s left of the given string.
    PIPE ROW(v_string);

    EXIT;
    END IF;
    END LOOP;

    RETURN;
    EXCEPTION
    WHEN NO_DATA_NEEDED THEN
    — This is done to not waste memory. It’s possible that the query using this function doesn’t need all the rows
    — returned. That causes this exception to get thrown by Oracle, but doesn’t cause crashing.

    RETURN;
    END split_string;

    3. Use that function in a query
    SELECT *
    FROM TABLE(split_string(column1, ‘,’));

Comments are closed.