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)