SYSK 136: INSERT SHRED(DelimitedString) INTO TABLE MyTable
In SYSK 131, Bill Wendel shows us how to convert a SQL string representing a boolean (e.g. ‘T’, ‘Y’, ‘1’, ‘N’, etc.) to a bit.
In this post (again, special thanks to Bill for creating and allowing me to post this function), you have a user defined function, that takes a varchar string containing a delimited list of values, and converts it to a table that you can join to, which, will likely yield better performance than executing dynamic SQL with WHERE myvalue in (delimitedList) type of query.
Yes, it’s not quite syntactically identical to the title of this post, but the net result is the same…
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF EXISTS(Select * from sysobjects
Where name='Split' AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.Split
GO
CREATE FUNCTION dbo.Split (
@sDelimitedList varchar(4000)
, @sDelimiter varchar(10)
)
RETURNS @Values Table (
ItemIndex int
, ItemValue varchar(100)
)
AS
/* ********************************************************************
** Name: dbo.Split
** Ver: SQL 2000 and later
** Desc: Parses a delimited list of values from a single string
** into individual values, as returned in a table.
** Delimiter can be commas, semicolons, etc., as specified
** in the @sDelimiter parameter. Leading and trailing
** Spaces are trimmed from each value.
** Auth: Bill Wendel
** Date: 06/24/2002
**
***********************************************************************
**
** CHANGE HISTORY
***********************************************************************
** Date: Author: Description:
** ________ __________ __________________________________________
** 06/24/02 wwendel Created procedure
** ***************************************************************** */
BEGIN
/* Example Call:
Select * from dbo.Split('ABC,DEF,GHI',',')
or
Select s.*
From Site s
join dbo.Split('Anaheim,Mesa,St. Louis',',') SiteList
on SiteName = SiteList.ItemValue
*/
Declare @pos0 int
, @pos1 int
, @nIndex int
, @sValue varchar(100)
, @nDelimiterLength int
Select @nIndex = 0
, @pos0 = 1
, @pos1 = charindex(@sDelimiter,@sDelimitedList)
, @nDelimiterLength = datalength(@sDelimiter)
While @pos1>0
BEGIN
Set @sValue = ltrim(rtrim(substring(@sDelimitedList,@pos0,@pos1-@pos0)))
Set @pos0 = @pos1 + @nDelimiterLength
if Datalength(@sValue)>0
BEGIN
Select @nIndex = @nIndex + 1
insert @values (ItemIndex, ItemValue) Values ( @nindex, @sValue)
END
Set @pos1 = charindex(@sDelimiter,@sDelimitedList,@pos0)
END
If @pos1=0 and @pos0 <= datalength(@sDelimitedList)
BEGIN
Set @sValue = ltrim(rtrim(substring(@sDelimitedList,@pos0,datalength(@sDelimitedList) - @pos0+ 1)))
END
Else If (@pos0 < datalength(@sDelimitedList)) or @Pos0=1
BEGIN
Set @sValue = ltrim(rtrim(substring(@sDelimitedList,@pos1,datalength(@sDelimitedList) - @pos1 + 1)))
END
Else Set @sValue=''
if datalength(@sValue)>0
BEGIN
Select @nIndex = @nIndex + 1
insert @values (ItemIndex, ItemValue) Values ( @nindex, @sValue)
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO