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
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
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) = ',';
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)
SET @fromIndex = @seperatiorAtIndex + 1
SET @seperatiorAtIndex = CHARINDEX(@separator, @valueList, @fromIndex)
END
Result:
SELECT * FROM @tblValue;
SELECT Value FROM @tblValue;
Exclude null
or empty:
SELECT * FROM @tblValue
WHERE LTRIM(RTRIM(UPPER(Value))) NOT IN ('', 'NULL');
Create Table-Valued FUNCTION
Here, we are going to create a helper function:
CREATE FUNCTION fnSplit(@valueList NVARCHAR(MAX), @separator NVARCHAR(10))
RETURNS @tblValue TABLE(Value NVARCHAR(MAX))
AS
BEGIN
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)
SET @fromIndex = @seperatiorAtIndex + 1
SET @seperatiorAtIndex = CHARINDEX(@separator, @valueList, @fromIndex)
END
RETURN
END;
Using this helper function like:
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
(
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
GROUP BY Id, StoreId, RoleName
This query in doing recursion calls. Depending on list size, we may need to include OPTION(MAXRECURSION 0)
in the query.
History
- 19th February, 2019: Initial version
- 23rd June, 2023: Second version