SQL Server: Doing String Split And Join

• Download ListToString_StringToList
• Download row_to_rows

Introduction

String splitting and joining are common in most programming languages like C#, JAVA, PHP. But I did not find any option in SQL SERVER. Let's create some code to do the same in SQL Server database.

It is always good to know a few things like CONVERT, STUFF, CHARINDEX, SUBSTRING, LTRIM, RTRIM, XML PATH() while working with string objects in SQL Server.

Join: List to List String

Here, we are going to convert (1), (2), (3), (4), (NULL) table values to NULL,1,2,3,4 string.

DECLARE @tblId TABLE (Id BIGINT NULL);
INSERT INTO @tblId VALUES (1), (2), (3), (4), (NULL);
SELECT * FROM @tblId;

DECLARE @separator NVARCHAR(10) = ',';
SELECT STUFF((SELECT @separator + COALESCE(CONVERT(NVARCHAR(MAX), Id), 'NULL') 
              FROM @tblId        /*to deselect NULL, add WHERE condition*/
              ORDER BY Id
              FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)')
              , 1, 1, '') AS Ids;

Usage Like AGGREGATE Function

DECLARE @tblUserGroup TABLE (GroupId INT, UserId INT NULL);
INSERT INTO @tblUserGroup VALUES
(1, 1), (1, 2), (1, 3),
(2, 4), (2, 5), (2, 6),
(3, NULL);
SELECT * FROM @tblUserGroup;

DECLARE @separator CHAR = ',';
SELECT 
    GroupId, 
    COUNT(UserId) AS TotalUser,
    STUFF((SELECT @separator + CONVERT(NVARCHAR(MAX), UserId) 
            FROM @tblUserGroup AS uc
            WHERE uc.GroupId = g.GroupId        /*to deselect NULL, add WHERE condition*/
            ORDER BY UserId
            FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)')
            , 1, 1, '') AS UserIds
FROM @tblUserGroup AS g
GROUP BY GroupId

Function with a user-defined type is another option, but again, we have to declare variable tables. But it would be better if we can create an AGGREGATE function or CLR.

Split: List String to List

This feature is now defaulted as STRING_SPLIT at SQL Servers starting from 2016 https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017.

But for the old versions, we can do:

DECLARE @valueList NVARCHAR(MAX) = 'Dan, Han, Mos, Ben,, ,null, Null, NULL';
DECLARE @separator NVARCHAR(10) = ',';

/*split process*/
DECLARE @tblValue TABLE(Value NVARCHAR(MAX));
DECLARE @fromIndex INT, @seperatiorAtIndex INT, @value NVARCHAR(100);
SET @fromIndex = 1;
SET @seperatiorAtIndex = CHARINDEX(@separator, @valueList)

WHILE @fromIndex < LEN(@valueList) + 1
BEGIN
    IF @seperatiorAtIndex = 0  
        SET @seperatiorAtIndex = LEN(@valueList) + 1
    SET @value = LTRIM(RTRIM(SUBSTRING_
                 (@valueList, @fromIndex, @seperatiorAtIndex - @fromIndex))); 
   
    INSERT INTO @tblValue VALUES(@value) -- inserting to the table  
   
    SET @fromIndex = @seperatiorAtIndex + 1
    SET @seperatiorAtIndex = CHARINDEX(@separator, @valueList, @fromIndex)  
END

Result:

/*result*/
SELECT * FROM @tblValue;
SELECT Value FROM @tblValue;

Exclude null or empty:

SELECT * FROM @tblValue
WHERE LTRIM(RTRIM(UPPER(Value))) NOT IN ('', 'NULL');    /*exclude null or empty*/

Create Table-Valued FUNCTION

Here, we are going to create a helper function:

/*create table-valued function*/
CREATE FUNCTION fnSplit(@valueList NVARCHAR(MAX), @separator NVARCHAR(10))
RETURNS @tblValue TABLE(Value NVARCHAR(MAX))
AS 
BEGIN
    /*split process*/
    DECLARE @fromIndex INT, @seperatiorAtIndex INT, @value NVARCHAR(100);
    SET @fromIndex = 1;
    SET @seperatiorAtIndex = CHARINDEX(@separator, @valueList)

    WHILE @fromIndex < LEN(@valueList) + 1
    BEGIN
           IF @seperatiorAtIndex = 0  
               SET @seperatiorAtIndex = LEN(@valueList) + 1
           SET @value = LTRIM(RTRIM(SUBSTRING_
               (@valueList, @fromIndex, @seperatiorAtIndex - @fromIndex))); 
  
           INSERT INTO @tblValue VALUES(@value) -- inserting to the table  
   
           SET @fromIndex = @seperatiorAtIndex + 1
           SET @seperatiorAtIndex = CHARINDEX(@separator, @valueList, @fromIndex)  
    END

    RETURN
END;

Using this helper function like:

/*using that function*/
DECLARE @valueList NVARCHAR(MAX) = 'Dan, Han, Mos, Ben';
DECLARE @separator NVARCHAR(10) = ',';
SELECT Value FROM fnSplit(@valueList, @separator);

Please find the necessary SQL files as an attachment. Rather than running the whole script altogether, run it section wise.

Split: Single Row to Multiple Rows

Here, we are splitting Roles column value and populating multiple rows form a single row.

DECLARE @tblSettings TABLE (
    Id INT IDENTITY(1, 1),
    StoreId INT,
    Roles VARCHAR(MAX) NULL
);
INSERT INTO @tblSettings (StoreId, [Roles])
VALUES
(10, 'A, B,C , , D ,'),
(20, ''),
(30, NULL);
SELECT * FROM @tblSettings;

WITH
RoleNamesSettingSplitted(Id, StoreId, RoleName, RemainingRoleNames) AS
(
    /*recursion, may need to use OPTION(MAXRECURSION 0) at client*/
    SELECT
        Id,
        StoreId,
        LTRIM(RTRIM(CAST(LEFT(Roles, CHARINDEX(',', Roles + ',') - 1) AS VARCHAR(MAX)))),
        CAST (STUFF(Roles, 1, CHARINDEX(',', Roles + ','), '') AS VARCHAR(MAX))
    FROM @tblSettings 
    WHERE Roles IS NOT NULL AND LTRIM(RTRIM(Roles)) <> ''
    UNION ALL
    SELECT
        Id,
        StoreId,
        LTRIM(RTRIM(CAST(LEFT(RemainingRoleNames, _
              CHARINDEX(',', RemainingRoleNames + ',') - 1) AS VARCHAR(MAX)))),
        CAST (STUFF(RemainingRoleNames, 1, _
              CHARINDEX(',', RemainingRoleNames + ','), '') AS VARCHAR(MAX))
    FROM RoleNamesSettingSplitted
    WHERE LTRIM(RTRIM(RemainingRoleNames)) > ''
)
SELECT 
    Id,
    StoreId,
    RoleName
FROM RoleNamesSettingSplitted
WHERE LEN(RoleName) > 0                /*ignore empty values*/
GROUP BY Id, StoreId, RoleName        /*ignore duplicates*/
--OPTION(MAXRECURSION 0)

This query in doing recursion calls. Depending on list size, we may need to include OPTION(MAXRECURSION 0) in the query.

History

• 19^th February, 2019: Initial version
• 23^rd June, 2023: Second version