USE [SampleDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[split_delimited_int]
(
@RecordIDList VARCHAR(MAX),
@Delimiter VARCHAR(10) = ','
)
RETURNS @ReturnValues TABLE (Number INT)
AS
BEGIN
DECLARE @RecordID VARCHAR(10)
DECLARE @Start INT = 0
DECLARE @Pos INT = 1
SET @RecordIDList = @RecordIDList + @Delimiter
SET @Pos = CHARINDEX(@Delimiter, @RecordIDList, 1)
WHILE @Pos > 0 BEGIN
SET @RecordID = LTRIM(RTRIM(SUBSTRING(@RecordIDList, @Start, @Pos - @Start)))
IF @RecordID <> ''
INSERT INTO @ReturnValues (Number) VALUES (CAST(@RecordID AS Int))
SET @Start = @Pos + len(@Delimiter)
SET @Pos = CHARINDEX(@Delimiter, @RecordIDList, @Start)
END
RETURN
END
Example usage
USE [SampleDB]
GO
SELECT * FROM dbo.split_delimited_int ('1,2,3, , ,3', ',')
Output
| Number |
|--------|
| 1 |
| 2 |
| 3 |
| 3 |
Split single delimeter string to table
A table-valued function that splits a string into rows of substrings, based on a specified separator character.
USE [SampleDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[split_string]
(
@param NVARCHAR(max),
@delimiter CHAR(1)
)
RETURNS @t TABLE (val NVARCHAR(max), seq INT)
AS
BEGIN
SET @param += @delimiter
;WITH a AS
(
SELECT
CAST(1 as BIGINT) f,
CHARINDEX(@delimiter, @param) t,
1 seq
UNION ALL
SELECT
t + 1,
CHARINDEX(@delimiter, @param, t + 1),
seq + 1
FROM a
WHERE CHARINDEX(@delimiter, @param, t + 1) > 0
)
INSERT @t
SELECT SUBSTRING(@param, f, t - f), seq FROM a
OPTION (maxrecursion 0);
RETURN;
END
Example usage
USE [SampleDB]
GO
SELECT * FROM dbo.split_string ('ab,c,1,2,c', ',')
Output
| val | seq |
|-----|-----|
| ab | 1 |
| c | 2 |
| 1 | 3 |
| 2 | 4 |
| c | 5 |
STRING_SPLIT (Transact-SQL)
If you are using SQL Server 2016 and later, you can use the built-in function STRING_SPLIT.
--example 1
SELECT ProductId, Name, Tags
FROM Product
JOIN STRING_SPLIT('1,2,3',',')
ON value = ProductId;
--example 2
DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'
SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';
The preceding STRING_SPLIT usage is a replacement for a common anti-pattern. Such an anti-pattern can involve the creation of a dynamic SQL string in the application layer or in Transact-SQL. Or an anti-pattern can be achieved by using the LIKE operator. See the following example SELECT statement:
SELECT ProductId, Name, Tags
FROM Product
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';