How to Split comma separated string in to rows?

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

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.