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