User Tools

Site Tools


notes:sql:techniques_splitfunctions

Split Functions in SQL

These are custom functions that split a comma-separated list of values into a variable of type TABLE:

IF OBJECT_ID('dbo.fun_SplitStringToGuid') IS NOT NULL DROP FUNCTION dbo.fun_SplitStringToGuid
GO
CREATE FUNCTION dbo.fun_SplitStringToGuid(@List AS VARCHAR(2000)) 
RETURNS @T TABLE (N UNIQUEIDENTIFIER)
AS
BEGIN
    DECLARE @P1 INT, @P2 INT, @S VARCHAR(100), @N UNIQUEIDENTIFIER
 
    SET @List = REPLACE(RTRIM(LTRIM(@List))+',', ',,', ',')
 
    SET @P1=1
    SET @P2=1
    WHILE @P2>0
    BEGIN
        SET @P2 = CHARINDEX(',',@List,@P1)
        IF @P2<>0 
        BEGIN
            SET @S = SUBSTRING(@List,@P1,@P2-@P1)
            -- TODO: Check if @S represents a valid GUID
            SET @N = CAST(@S AS UNIQUEIDENTIFIER)
            INSERT INTO @T VALUES(@N)
            SET @P1 = @P2+1
        END
    END
 
    RETURN
END
GO
IF OBJECT_ID('dbo.fun_SplitStringToInt') IS NOT NULL DROP FUNCTION dbo.fun_SplitStringToInt
GO
CREATE FUNCTION dbo.fun_SplitStringToInt(@List AS VARCHAR(2000)) 
RETURNS @T TABLE (N INT)
AS
BEGIN
    DECLARE @P1 INT, @P2 INT, @S VARCHAR(20), @N INT
 
    SET @List = REPLACE(RTRIM(LTRIM(@List))+',', ',,', ',')
 
    SET @P1=1
    SET @P2=1
    WHILE @P2>0
    BEGIN
        SET @P2 = CHARINDEX(',',@List,@P1)
        IF @P2<>0 
        BEGIN
            SET @S = SUBSTRING(@List,@P1,@P2-@P1)
            -- TODO: Check if @S represents a valid INT
            SET @N = CAST(@S AS INT)
            INSERT INTO @T VALUES(@N)
            SET @P1 = @P2+1
        END
    END
 
    RETURN
END
GO
IF OBJECT_ID('dbo.fun_SplitStringToInt2D') IS NOT NULL DROP FUNCTION dbo.fun_SplitStringToInt2D
GO
CREATE FUNCTION dbo.fun_SplitStringToInt2D(@List1 AS VARCHAR(2000), @List2 AS VARCHAR(2000)) 
RETURNS @T TABLE (N INT, M INT)
AS
BEGIN
    DECLARE @P1 INT, @P2 INT, @SN VARCHAR(20), @N INT
    DECLARE @P3 INT, @P4 INT, @SM VARCHAR(20), @M INT
 
    SET @List1 = REPLACE(RTRIM(LTRIM(@List1))+',', ',,', ',')
    SET @List2 = REPLACE(RTRIM(LTRIM(@List2))+',', ',,', ',')
 
    SET @P1=1
    SET @P2=1
    SET @P3=1
    SET @P4=1
    WHILE @P2>0 AND @P4>0
    BEGIN
        SET @P2 = CHARINDEX(',',@List1,@P1)
        SET @P4 = CHARINDEX(',',@List2,@P3)
        IF @P2<>0 AND @P4<>0
        BEGIN
            SET @SN = SUBSTRING(@List1,@P1,@P2-@P1)
            SET @SM = SUBSTRING(@List2,@P3,@P4-@P3)
            -- TODO: Check if @S represents a valid INT
            SET @N = CAST(@SN AS INT)
            SET @M = CAST(@SM AS INT)
 
            INSERT INTO @T VALUES(@N,@M)
            SET @P1=@P2+1
            SET @P3=@P4+1
        END
    END
 
    RETURN
END
GO
IF OBJECT_ID('dbo.fun_SplitStringToVarchar2D') IS NOT NULL DROP FUNCTION dbo.fun_SplitStringToVarchar2D
GO
CREATE FUNCTION dbo.fun_SplitStringToVarchar2D(@List1 AS VARCHAR(2000), @List2 AS VARCHAR(2000)) 
RETURNS @T TABLE (N INT, S VARCHAR(100))
AS
BEGIN
    DECLARE @P1 INT, @P2 INT, @SN VARCHAR(20), @N INT
    DECLARE @P3 INT, @P4 INT, @S VARCHAR(100)
 
    SET @List1 = REPLACE(RTRIM(LTRIM(@List1))+',', ',,', ',')
    SET @List2 = REPLACE(RTRIM(LTRIM(@List2))+',', ',,', ',')
 
    SET @P1=1
    SET @P2=1
    SET @P3=1
    SET @P4=1
    WHILE @P2>0 AND @P4>0
    BEGIN
        SET @P2 = CHARINDEX(',',@List1,@P1)
        SET @P4 = CHARINDEX(',',@List2,@P3)
 
        IF @P2<>0 AND @P4<>0
        BEGIN
            SET @SN = SUBSTRING(@List1,@P1,@P2-@P1)
            SET @S = SUBSTRING(@List2,@P3,@P4-@P3)
            -- TODO: Check if @S represents a valid VARCHAR
            SET @N = CAST(@SN AS INT)
 
            INSERT INTO @T VALUES(@N,@S)
            SET @P1=@P2+1
            SET @P3=@P4+1
        END
    END
 
    RETURN
END
GO
/*
	Function: fun_SplitString
	Description: Splits a given string into substrings.
 
	Input: 
	@Separator - a word delimiter
	@Text - a list of words separated by @Separator; maximum length of all words together 
		with separators: 4000; maximum length of a single word: 50
 
	Output:
	A table variable containing the substrings.
*/
IF OBJECT_ID('dbo.fun_SplitString') IS NOT NULL DROP FUNCTION dbo.fun_SplitString
GO
CREATE FUNCTION dbo.fun_SplitString(@Text AS NVARCHAR(4000), @Separator CHAR(1)) 
RETURNS @T TABLE (S NVARCHAR(50))
AS
BEGIN
	IF LEN(@Text)<=1
	BEGIN
		INSERT INTO @T VALUES(@Text)
	END
	ELSE
		BEGIN
		DECLARE @P1 INT, @P2 INT, @S NVARCHAR(50)
 
		SET @Text = RTRIM(LTRIM(@Text))
		IF SUBSTRING(@Text,LEN(@Text),1)<>@Separator SET @Text=@Text+@Separator
 
		SET @P1=1
		SET @P2=1
		WHILE @P2>0
		BEGIN
			SET @P2 = CHARINDEX(@Separator,@Text,@P1)
			IF @P2<>0 
			BEGIN
				SET @S = SUBSTRING(@Text,@P1,@P2-@P1)
				IF @S<>'' INSERT INTO @T VALUES(@S)
				SET @P1=@P2+1
			END
		END
	END
 
	RETURN
END
GO
 
-- test
SELECT * FROM dbo.fun_SplitString('9781572316966 9781598220537 9781598220544 9781584505594 0735616531', ' ')

Examples of usage:

Example #1:

DECLARE @ids TABLE (N INT)
DECLARE @MultipleFlagID VARCHAR(200)
SET @MultipleFlagID = '8,34,12,67'
 
INSERT INTO @ids SELECT * FROM dbo.fun_SplitStringToInt(@MultipleFlagID)
 
SELECT * FROM @ids
 
-- Results:
N
-----------
8
34
12
67
 
-- Enumerate elements using a cursor
DECLARE @N INT
DECLARE cur CURSOR FOR SELECT N FROM @ids
OPEN cur
FETCH NEXT FROM cur INTO @N
 
WHILE @@FETCH_STATUS=0
BEGIN
    -- Do something with @N
    PRINT CAST(@N AS VARCHAR(2))
    FETCH NEXT FROM cur INTO @N
END

Example #2:

DECLARE @ids TABLE (N UNIQUEIDENTIFIER)
DECLARE @MultipleClientID VARCHAR(500)
SET @MultipleClientID = 'DEDD0712-F675-4701-A0E0-AF4481B2D30D,' +
    'A2C05968-8119-4152-9B2C-B55114DE9134,' +
    '7E5E1C66-809D-46EF-B715-9186994A4F50'
 
INSERT INTO @ids SELECT * FROM dbo.fun_SplitStringToGuid(@MultipleClientID)
 
SELECT * FROM @ids
 
-- Results:
N
-----------
DEDD0712-F675-4701-A0E0-AF4481B2D30D
A2C05968-8119-4152-9B2C-B55114DE9134
7E5E1C66-809D-46EF-B715-9186994A4F50

Example #3:

DECLARE @ids TABLE (N INT)
DECLARE @CustomerIDs VARCHAR(200), @NumOrders VARCHAR(500)
SET @CustomerIDs = '2,6,23,27'
SET @NumOrders = '120,300,80,2060'
 
DECLARE @Customers TABLE (ID INT, Cnt INT)
INSERT INTO @Customers SELECT * FROM dbo.fun_SplitStringToInt2D(@CustomerIDs, @NumOrders)
 
SELECT * FROM @Customers
 
-- Results:
ID    Cnt
----- -----
2     120
6     300
23    80
27    2060
notes/sql/techniques_splitfunctions.txt · Last modified: 2020/08/26 (external edit)