Function to Split a Delimited String into a Table

,

This function splits a delimited string (up to 4000 characters long) into a single column table. The delimiter can be specified at the time of execution. If not specified, the delimiter defaults to a comma. The default length of each value is 100, but that can easily be changed.
An example for usage:
DECLARE @string NVARCHAR(4000)
DECLARE @instruments TABLE (instrument NVARCHAR(100))
SET @string = 'guitar, flute, snare drum, bass drum, trumpet'
INSERT INTO @instruments (instrument)
SELECT value FROM dbo.fnDStringToTable(@string, ',')
SELECT instrument FROM @instruments

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

--This UDF will split a delimited list into a single column table.

CREATE FUNCTION dbo.fnDStringToTable
(
	  @list NVARCHAR(4000)
	, @delimiter NCHAR(1) = ',' --Defaults to CSV
)
RETURNS 
@tableList TABLE(
	value NVARCHAR(100)
	)
AS
BEGIN
	DECLARE @value    NVARCHAR(100)
	DECLARE @position INT

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

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

		END
	END	
	RETURN
END
GO

Rate

3.17 (6)

Share

Share

Rate

3.17 (6)