This improved Split function allows for multi-byte delimiters, optional null values, and optional null value substitution.
2007-10-02 (first published: 2002-06-20)
15,451 reads
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