Split string function in SQL Server to break Comma separated strings

Sometimes we need to insert – save multiple checked checkbox values in SQL Server.For that we can use Split function.

Split function of SQL Server is not in-built function,We have to add it manually in our Table-valued function as you can see in below image.

Split function in SQL Server

Below you can find query for splitting the string in SQL.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[Split]   (   @RowData nvarchar(MAX), @SplitOn nvarchar(5) )     RETURNS @ReturnValue TABLE   (Data NVARCHAR(MAX))   AS BEGIN Declare @Counter int Set @Counter = 1 While (Charindex(@SplitOn,@RowData)>0) Begin     Insert Into @ReturnValue (data)     Select Data =       ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))   Set @RowData =       Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))   Set @Counter = @Counter + 1   End Insert Into @ReturnValue (data)   Select Data = ltrim(rtrim(@RowData))   Return   END

Now,I will explain how to use this split function.Once you have created above split function in your database,It will add to Table-valued function.

Now whenever you want to separate string by comma,just pass your string to the function,it will return Data column.

Here’s I will use my table that I created in Case Statement in SQL Server.You can find create,insert script for Product table from that article.On select of product table,you will find below image.

Note : To use this tutorial,allow NULL values to Price & Availability columns.

Now,here I will pass ProductNames variable as comma separated string,in your case it may be checked items or anything else.But to use split function of this SQL server,you need to pass comma separated string to function.

I will add MotherBoard & other 2 products in table using below script.You can also make stored procedure according to your requirement.

1 2 3 4

DECLARE @ProductNames Varchar(MAX) SET @ProductNames='MotherBoard,Graphics Card,HardDisk' INSERT INTO dbo.product(ProductName) SELECT Data FROM dbo.Split(@ProductNames,',')

Now after running above script,you can find below image Product table as 3 rows inserted in Product table.

This is how you can save multiple checked checkbox values tho SQL Server table.

Hope,you enjoy this article.You can also check other SQL Tutorial.

Second WAY:

Introduction: 

Here I will explain simple split function in SQL Server to split comma separated string into table values inSQL Server database or How to split comma separated string with custom split() function in SQL Server. 

To split comma separated string in SQL Server we need to write custom method for that we need to create one function like as shown below

Custom Split function to split comma separated string into table

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1)) RETURNS @Results TABLE (Items nvarchar(4000)) AS BEGIN DECLARE @INDEX INT DECLARE @SLICE nvarchar(4000) -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z --     ERO FIRST TIME IN LOOP SELECT @INDEX = 1 WHILE @INDEX !=0 BEGIN -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE IF @INDEX !=0 SELECT @SLICE = LEFT(@STRING,@INDEX - 1) ELSE SELECT @SLICE = @STRING -- PUT THE ITEM INTO THE RESULTS SET INSERT INTO @Results(Items) VALUES(@SLICE) -- CHOP THE ITEM REMOVED OFF THE MAIN STRING SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX) -- BREAK OUT IF WE ARE DONE IF LEN(@STRING) = 0 BREAK END RETURN END

Once we create custom function Split() run sample query like as shown below

SELECT items FROM [dbo].[Split] ('861,739,10,1670', ',')

Once we run above query we will get output like as shown below