Warning: Trying to access array offset on value of type null in /home/customer/www/mozartec.com/public_html/wp-content/plugins/pressapps-login-access/includes/skelet/path.php on line 408
Convert CSV To Rows Using Sql Server Function And Cross Apply - Mozartec
Mozart Al Khateeb

Full Stack

Mobile

.Net Developer

ERP Developer

Mozart Al Khateeb

Full Stack

Mobile

.Net Developer

ERP Developer

Blog Post

Convert CSV To Rows Using Sql Server Function And Cross Apply

March 17, 2019 Database
Convert CSV To Rows Using Sql Server Function And Cross Apply

Create a reusable function to split Delimiter separated string into row 

CREATE FUNCTION [dbo].[Split](@String NVARCHAR(MAX), @Delimiter char(1)) 
returns @temptable TABLE (items NVARCHAR(MAX)) 
as 
begin 
declare @idx int 
declare @slice NVARCHAR(MAX) 

select @idx = 1 
if len(@String)<1 or @String is null return 

while @idx!= 0 
begin 
set @idx = charindex(@Delimiter,@String) 
if @idx!=0 
set @slice = left(@String,@idx - 1) 
else 
set @slice = @String 

if(len(@slice)>0)
insert into @temptable(Items) values(@slice) 

set @String = right(@String,len(@String) - @idx) 
if len(@String) = 0 break 
end 
return 
end

Test It

SELECT tb.a 
INTO #tmp
FROM
(SELECT '1,23,5' a
UNION ALL
SELECT '5,9,15' a) tb;

-------------------------------------------

SELECT * FROM #tmp;

-------------------------------------------

SELECT
items
FROM (SELECT a
FROM #tmp) tb
CROSS APPLY dbo.Split(tb.a, ',');

Taggs:
Write a comment