Table Valued Functions

Split delimeter string to integer

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)) + ',%';