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

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,327 reads

Technical Article

DATEMY - Covnerts and validates MM/YY, MM/YYYY

If you've ever needed a convenient and quick way to convert/validate what could possibly be a mm/yy or mm/yyyy date to mm/dd/yyyy (date of month is 01) check out this custom function.  Eg:  select dbo.datemy('04/02') outputs smalldatetime '2002-04-01 00:00:00'.  Select dbo.datemy('04/2002') outputs the same smalldatetime '2002-04-01 00:00:00'.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-02-15

870 reads