How to Split Comma Separated String Value in SQL Server

Tutorialsrack 29/10/2023 SQL SQL Server

In this article, we will learn about how to split comma-separated string values in SQL Server.

If you are using SQL Server 2016 and later, Microsoft introduced a new built-in table value function STRING_SPLIT().

STRING_SPLIT(your_column or String_value, ',');

This function takes two parameters:

• The first parameter is a string value. 
• The second parameter is a separator.

Example: Split a Comma-Separated String Value in SQL Server 2016 and Later

DECLARE @String VARCHAR(150) = 'ASP.NET,ADO.NET,JAVA,PYTHON,JAVASCRIPT,GO,RUBY,PHP';
SELECT VALUE AS 'Programming Language' FROM STRING_SPLIT(@String, ',');

Programming Language
----------------------------------------------------------
ASP.NET
ADO.NET
JAVA
PYTHON
JAVASCRIPT
GO
RUBY
PHP

If you are using SQL Server 2014 and earlier versions, then there is no built-in function for splitting a string value. So for SQL Server 2014 and earlier versions, you can create your own custom split function. Here's an example of a simple custom split function:

Example: Split a Comma-Separated String Value in SQL Server 2014 and Earlier Version

Custom function for splitting a value in SQL Server 2014 and earlier versions:

CREATE FUNCTION dbo.SplitString
(
    @String NVARCHAR(MAX),
    @Delimiter NVARCHAR(5)
)
RETURNS @Result TABLE (Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @StartIndex INT, @EndIndex INT
    
    SET @StartIndex = 1
    IF SUBSTRING(@String, LEN(@String) - 1, 1) <> @Delimiter
    BEGIN
        SET @String = @String + @Delimiter
    END

    WHILE CHARINDEX(@Delimiter, @String, @StartIndex) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @String, @StartIndex)
        INSERT INTO @Result (Value)
        VALUES (SUBSTRING(@String, @StartIndex, @EndIndex - @StartIndex))
        SET @StartIndex = @EndIndex + 1
    END

    RETURN
END

Here, you can then use this custom function to split the string:

DECLARE @String VARCHAR(150) = 'ASP.NET,ADO.NET,JAVA,PYTHON,JAVASCRIPT,GO,RUBY,PHP';
SELECT VALUE AS 'Programming Language' FROM dbo.SplitString(@String, ',');

Programming Language
----------------------------------------------------------
ASP.NET
ADO.NET
JAVA
PYTHON
JAVASCRIPT
GO
RUBY
PHP

I hope this article will help you to understand how to split comma-separated string values in SQL Server.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts