A string split function for SQL Server
This is my string split function for SQL Server.
-- ================================================
-- Returns a table that contains the substrings in the source string that are delimited by the specified character.
-- Parameters specify the maximum number of substrings to return and whether to return empty strings.
-- ================================================
ALTER FUNCTION [dbo].[ufn_Split]
(
-- The source string to split.
@StringToSplit NVARCHAR(MAX),
-- The character that delimit the substrings,
-- NULL contains no delimiters.
@Separator NCHAR(1),
-- The maximum number of substrings to return.
@ReturnCount INT = NULL,
-- Specify 0 or NULL to omit empty strings in the table returned,
-- otherwise to include empty strings from the table returned.
@SplitOption BIT = 1
)
RETURNS @ReturnTable TABLE
(
[String] NVARCHAR(MAX)
)
AS
BEGIN
-- Test the @StringToSplit and @ReturnCount parameters
IF ((@StringToSplit IS NULL) OR (LEN(@StringToSplit) = 0))
OR ((@ReturnCount IS NOT NULL) AND (@ReturnCount <= 0))
GOTO FUNCTION_END;
-- Test the @Separator parameter
IF (@Separator IS NULL) OR (LEN(@Separator) = 0)
BEGIN
INSERT @ReturnTable VALUES (@StringToSplit);
GOTO FUNCTION_END;
END
-- Split the source string
DECLARE @Result TABLE ([String] NVARCHAR(MAX));
DECLARE @Length AS INT = LEN(@StringToSplit),
@Start AS INT = 0;
WHILE @Start <= @Length
BEGIN
DECLARE @Next AS INT = CHARINDEX(@Separator, @StringToSplit, @Start);
DECLARE @Sub NVARCHAR(MAX);
IF @Next >= 1
BEGIN
SET @Sub = SUBSTRING(@StringToSplit, @Start, @Next - @Start);
INSERT @Result VALUES (@Sub);
SET @Start = @Next + 1;
END
ELSE
BEGIN
IF @Start <= @Length
BEGIN
SET @Sub = SUBSTRING(@StringToSplit, @Start, @Length - @Start);
INSERT @Result VALUES (@Sub);
END
BREAK
END
END
IF SUBSTRING(@StringToSplit, @Length, 1) = @Separator
INSERT INTO @Result VALUES ('');
-- Clean results according to the @SplitOption parameter
IF (@SplitOption = 0) OR (@SplitOption IS NULL)
DELETE FROM @Result WHERE [String] = '';
-- Limit the return count
DECLARE @ResultCount AS INT;
SELECT @ResultCount = COUNT([String]) FROM @Result;
IF (@ReturnCount IS NOT NULL) AND (@ResultCount > @ReturnCount)
SET @ResultCount = @ReturnCount;
-- Add results to the to be returned table
DECLARE @String AS NVARCHAR(MAX);
DECLARE ResultCursor CURSOR FOR SELECT * FROM @Result;
OPEN ResultCursor;
FETCH NEXT FROM ResultCursor INTO @String;
WHILE (@@FETCH_STATUS = 0) AND (@ResultCount > 0)
BEGIN
INSERT @ReturnTable VALUES (@String);
FETCH NEXT FROM ResultCursor INTO @String;
SET @ResultCount = @ResultCount - 1;
END
CLOSE ResultCursor;
DEALLOCATE ResultCursor;
-- Return the function
FUNCTION_END:
RETURN;
END
-- Returns a table that contains the substrings in the source string that are delimited by the specified character.
-- Parameters specify the maximum number of substrings to return and whether to return empty strings.
-- ================================================
ALTER FUNCTION [dbo].[ufn_Split]
(
-- The source string to split.
@StringToSplit NVARCHAR(MAX),
-- The character that delimit the substrings,
-- NULL contains no delimiters.
@Separator NCHAR(1),
-- The maximum number of substrings to return.
@ReturnCount INT = NULL,
-- Specify 0 or NULL to omit empty strings in the table returned,
-- otherwise to include empty strings from the table returned.
@SplitOption BIT = 1
)
RETURNS @ReturnTable TABLE
(
[String] NVARCHAR(MAX)
)
AS
BEGIN
-- Test the @StringToSplit and @ReturnCount parameters
IF ((@StringToSplit IS NULL) OR (LEN(@StringToSplit) = 0))
OR ((@ReturnCount IS NOT NULL) AND (@ReturnCount <= 0))
GOTO FUNCTION_END;
-- Test the @Separator parameter
IF (@Separator IS NULL) OR (LEN(@Separator) = 0)
BEGIN
INSERT @ReturnTable VALUES (@StringToSplit);
GOTO FUNCTION_END;
END
-- Split the source string
DECLARE @Result TABLE ([String] NVARCHAR(MAX));
DECLARE @Length AS INT = LEN(@StringToSplit),
@Start AS INT = 0;
WHILE @Start <= @Length
BEGIN
DECLARE @Next AS INT = CHARINDEX(@Separator, @StringToSplit, @Start);
DECLARE @Sub NVARCHAR(MAX);
IF @Next >= 1
BEGIN
SET @Sub = SUBSTRING(@StringToSplit, @Start, @Next - @Start);
INSERT @Result VALUES (@Sub);
SET @Start = @Next + 1;
END
ELSE
BEGIN
IF @Start <= @Length
BEGIN
SET @Sub = SUBSTRING(@StringToSplit, @Start, @Length - @Start);
INSERT @Result VALUES (@Sub);
END
BREAK
END
END
IF SUBSTRING(@StringToSplit, @Length, 1) = @Separator
INSERT INTO @Result VALUES ('');
-- Clean results according to the @SplitOption parameter
IF (@SplitOption = 0) OR (@SplitOption IS NULL)
DELETE FROM @Result WHERE [String] = '';
-- Limit the return count
DECLARE @ResultCount AS INT;
SELECT @ResultCount = COUNT([String]) FROM @Result;
IF (@ReturnCount IS NOT NULL) AND (@ResultCount > @ReturnCount)
SET @ResultCount = @ReturnCount;
-- Add results to the to be returned table
DECLARE @String AS NVARCHAR(MAX);
DECLARE ResultCursor CURSOR FOR SELECT * FROM @Result;
OPEN ResultCursor;
FETCH NEXT FROM ResultCursor INTO @String;
WHILE (@@FETCH_STATUS = 0) AND (@ResultCount > 0)
BEGIN
INSERT @ReturnTable VALUES (@String);
FETCH NEXT FROM ResultCursor INTO @String;
SET @ResultCount = @ResultCount - 1;
END
CLOSE ResultCursor;
DEALLOCATE ResultCursor;
-- Return the function
FUNCTION_END:
RETURN;
END