# How to Split comma delimited string into a Table in SQL Server

- Link: https://techaid24.com/split-comma-delimited-string-sql-server/
- Published: 2025-11-22T13:36:31+06:00
- Author: Selim Ahmed

In this article, we'll take a look at Show 1) APPROACH 1: SQL Server 2016 STRING_SPLIT function 2) APPROACH 2: Using While Loop and SQL String Functions 3) APPROACH 3: Using XML In this post, we discuss best practices for split comma delimited string SQL Server. Many a time we come across a scenario where we pass a comma or any other character delimited string to stored procedure and in a stored procedure, we want to fetch data from other tables based on this delimited string. We can’t use this delimited string or split comma delimited string SQL directly in the clause as SQL treats it as one string. So, the solution for this is to get a table by splitting the delimited string by the delimiter character and then join this resultant table data with other table columns. In SQL Server we have a mechanism where we can take a table as an input parameter from the application code. So if you like to pass a table from your code with multiple rows instead of passing a delimited string to stored procedure and splitting it in SP, then you may like to read the article Table Type Parameters in SQL Server. But I would prefer passing the comma-delimited string to the stored procedure and splitting it in the SP. In SQL Server we have multiple approaches to achieve this. This article lists a couple of them. Please let me know which one you use and if you use some other approach let me know so that we can help the SQL Server developer community. APPROACH 1: SQL Server 2016 STRING_SPLIT function STRING_SPLIT is one of the new built-in table-valued functions introduced in SQL Server 2016. This table valued function splits the input string by the specified character separator and returns the output as a table. The below example shows how we can use the STRING_SPLIT function to splits the comma-separated string. SELECT * FROM STRING_SPLIT('TechAid24,Your,Technical,Consultant',',') 1234  SELECT * FROM STRING_SPLIT('TechAid24,Your,Technical,Consultant',',')  APPROACH 2: Using While Loop and SQL String Functions We can create a table-valued function like the below which is using WHILE loop and SQL String functions like CHARINDEX and SUBSTRING to split the string. This should work in all the versions of SQL Server. CREATE FUNCTION [dbo].StringSplit ( @String VARCHAR(MAX), @Separator CHAR(1) ) RETURNS @RESULT TABLE(Value VARCHAR(MAX)) AS BEGIN DECLARE @SeparatorPosition INT = CHARINDEX(@Separator, @String ), @Value VARCHAR(MAX), @StartPosition INT = 1 IF @SeparatorPosition = 0 BEGIN INSERT INTO @RESULT VALUES(@String) RETURN END SET @String = @String + @Separator WHILE @SeparatorPosition > 0 BEGIN SET @Value = SUBSTRING(@String , @StartPosition, @SeparatorPosition- @StartPosition) IF( @Value <> '' ) INSERT INTO @RESULT VALUES(@Value) SET @StartPosition = @SeparatorPosition + 1 SET @SeparatorPosition = CHARINDEX(@Separator, @String , @StartPosition) END RETURN END 1234567891011121314151617181920212223242526272829303132  CREATE FUNCTION [dbo].StringSplit(    @String  VARCHAR(MAX), @Separator CHAR(1))RETURNS @RESULT TABLE(Value VARCHAR(MAX))ASBEGIN     DECLARE @SeparatorPosition INT = CHARINDEX(@Separator, @String ),        @Value VARCHAR(MAX), @StartPosition INT = 1 IF @SeparatorPosition = 0    BEGIN   INSERT INTO @RESULT VALUES(@String)   RETURN  END     SET @String = @String + @Separator WHILE @SeparatorPosition > 0  BEGIN   SET @Value = SUBSTRING(@String , @StartPosition, @SeparatorPosition- @StartPosition)    IF( @Value <> ''  )     INSERT INTO @RESULT VALUES(@Value)      SET @StartPosition = @SeparatorPosition + 1   SET @SeparatorPosition = CHARINDEX(@Separator, @String , @StartPosition)  END         RETURNEND  The below example shows how we can use the above function to split the comma-delimited string SELECT * FROM StringSplit('TechAid24,Your,Technical,Consultant',',') 1234  SELECT *FROM StringSplit('TechAid24,Your,Technical,Consultant',',')  APPROACH 3: Using XML We can create a table-valued function like the below which is using the SQL XML feature to split the string. CREATE FUNCTION [dbo].StringSplitXML (     @String  VARCHAR(MAX), @Separator CHAR(1) ) RETURNS @RESULT TABLE(Value VARCHAR(MAX)) AS BEGIN      DECLARE @XML XML  SET @XML = CAST(     ('<i>' + REPLACE(@String, @Separator, '</i><i>') + '</i>')     AS XML)    INSERT INTO @RESULT  SELECT t.i.value('.', 'VARCHAR(MAX)')  FROM @XML.nodes('i') AS t(i)  WHERE t.i.value('.', 'VARCHAR(MAX)') <> ''    RETURN END 123456789101112131415161718192021  CREATE FUNCTION [dbo].StringSplitXML(    @String  VARCHAR(MAX), @Separator CHAR(1))RETURNS @RESULT TABLE(Value VARCHAR(MAX))ASBEGIN     DECLARE @XML XML SET @XML = CAST(    ('<i>' + REPLACE(@String, @Separator, '</i><i>') + '</i>')    AS XML)  INSERT INTO @RESULT SELECT t.i.value('.', 'VARCHAR(MAX)') FROM @XML.nodes('i') AS t(i) WHERE t.i.value('.', 'VARCHAR(MAX)') <> ''  RETURNEND  The below example shows how we can use the above function to split the comma-delimited string SELECT * FROM StringSplitXML('TechAid24,Your,Technical,Consultant',',') 123  SELECT * FROM StringSplitXML('TechAid24,Your,Technical,Consultant',',')  Best practice for split comma delimited string SQL Server