Technical Article

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)

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.17 (6)

You rated this post out of 5. Change rating

Related content

Technical Article

Write Reports from SQL to Disk (Even HTML!)

Ever need to write reports out to a folder? I have found that creating output files are SA-WEET! (and easy too) The sample script uses BCP to create an HTML file.This process works well for reports that need to be generated nightly and take too long to run in real time.  Use an SMTP mail […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-04-18

4,207 reads

Technical Article

DTS-Less and Jobless Approach to Table Freshness

How about getting the data when the table data is first queried?  The following script checks to see if the specified table (@MyTable) has been updated today.  If so, just return the data found.  If not, then get the data from a "fresher source".  Great for ASP that performs table reporting and needs "baked daily" […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-04-23

1,549 reads

Technical Article

Script to generate BCP Statements for a Database

The Script below creates a Stored Procedure that returns a Macro SQL Statement. When this SQL statement is executed, it inturn returns BCP Out Statements for all the tables in a given database. The database name should be passed as a parameter to the procedure. Before the procedure is created, the SA Password must be […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

1 (1)

You rated this post out of 5. Change rating

2002-01-07

3,775 reads