Technical Article

Improved Split function

,

This improved Split function allows for multi-byte delimiters, optional null values, and optional null value substitution.

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[fnSplit]'))
	DROP FUNCTION [dbo].fnSplit
GO

--This UDF will split a delimited list into a table.
CREATE FUNCTION dbo.fnSplit
(
	  @list NVARCHAR(4000)
	, @delimiter NVARCHAR(10) = N','
	, @include_null BIT = 0
	, @null_text NVARCHAR(10) = NULL
)
RETURNS @tableList TABLE(
	idx SMALLINT IDENTITY (1,1) PRIMARY KEY,
	value NVARCHAR(100) NULL
	)
AS
BEGIN
	DECLARE @value    NVARCHAR(100)
	DECLARE @position INT

	SET @list = LTRIM(RTRIM(@list))+ @delimiter
	SET @position = CHARINDEX(@delimiter, @list, 1)

	IF REPLACE(@list, @delimiter, '') <> ''
	BEGIN
		WHILE @position > 0
		BEGIN
			SET @value = LTRIM(RTRIM(LEFT(@list, @position - 1)))
			IF @include_null = 1 OR @value <> ''
			BEGIN
				IF @value = '' SET @value = @null_text
				INSERT INTO @tableList (value) 
				VALUES (@value)
			END
			SET @list = RIGHT(@list, LEN(@list) + 1 - LEN(@delimiter) - @position)
			SET @position = CHARINDEX(@delimiter, @list, 1)
		END
	END	
	RETURN
END
GO

Rate

3 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (1)

You rated this post out of 5. Change rating