Thursday, April 15, 2010

Converting a delimited string into a table

This isn't anything new, but we will be building upon this in the next post. At times you want to take a delimited string and have it returned in a table format so you can loop over it or do stuff with it; many people have done this before and I'm sure that I'm drawing on stuff I've seen before. This function works well, you call it like this:

SELECT *
FROM dbo.StringToTable('a,b,c,d',',')


Where the first parameter is the string to be converted and the second is the delimiter


USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--/***********************************************************************
--***
--*** Function: [StringToTable]
--*** Purpose: Takes a delimited string and returns a table
--***
--***
--*** Author: Eric Zierdt
--*** Date Created: 2010-04-15
--***
--*** Revision History
--*** Date Author Description
--*** 2010-04-15 ez Created
--*** select * from dbo.StringToTable('a,b,c,d',',')
--***********************************************************************/

CREATE FUNCTION [dbo].[StringToTable]
(
@String VARCHAR(MAX),
@Delimiter CHAR(1) = ','
)
RETURNS @ReturnList TABLE
(
ID INT,
TableValue VARCHAR(MAX)
)
AS BEGIN
DECLARE @ArrayList TABLE
(
ID INT, TableValue VARCHAR(MAX)
)
DECLARE @Value VARCHAR(MAX)
DECLARE @NextDelimiterLocation INT

----------------------------
-- REMOVE LEADING DELIMITER --
----------------------------
SET @String = LTRIM(RTRIM(@String)) +
CASE WHEN RIGHT(@String, 1) = @Delimiter THEN ''
ELSE @Delimiter
END

-------------------------------------------
-- SET THE LOCATION OF THE FIRST DELIMITER --
-------------------------------------------
SET @NextDelimiterLocation = ISNULL(CHARINDEX(@Delimiter, @String, 1), 0)

----------------------------------------
-- IF NO DELIMITER THEN SET THE WHOLE --
-- STRING TO THE FIRST ROW OF THE TABLE --
----------------------------------------
IF @NextDelimiterLocation = 0
BEGIN
INSERT INTO @ArrayList
(
ID
,TableValue
)
SELECT 1, @String
END
ELSE
BEGIN
DECLARE @Counter INT
SET @Counter = 0
WHILE @NextDelimiterLocation > 0
BEGIN
-----------------------------------------------------
-- SET THE VALUE PARAM TO DATA BEFORE THE DELIMITER --
-- THEN LOOP TO DO THE SAME FOR THE REST OF THE LIST --
-----------------------------------------------------
SET @Value = LTRIM(RTRIM(LEFT(@String, @NextDelimiterLocation - 1)))
IF LEN(@String) > 0 AND @NextDelimiterLocation <= LEN(@String)
BEGIN
SET @Counter = @Counter + 1
INSERT INTO @ArrayList ( ID, TableValue )
SELECT @Counter, @Value
END
SET @String = SUBSTRING(@String, @NextDelimiterLocation + LEN(@Delimiter), LEN(@String))
SET @NextDelimiterLocation = CHARINDEX(@Delimiter, @String, 1)
END
END
INSERT @ReturnList
(
ID, TableValue
)
SELECT ID, TableValue
FROM @ArrayList

RETURN
END



No comments:

Post a Comment