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.
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.
with Split_values as
(select ‘Sandeep,Rahul,Prathusha, Nirav’ Split_String from dual
select regexp_substr (str, ‘[^,]+’, 1, rownum) Values_Split
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.
Split comma separated String in Oracle complex example
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
regexp_substr (str, ‘[^,]+’, 1, rn) Split_Values
join (select rownum row_num
from (select max (length (regexp_replace (str, ‘[^,]+’))) + 1 Max_val
connect by level <= Max_val
where regexp_substr (str, ‘[^,]+’, 1, row_num) is not null
order by id;
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.