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


 

Comments (0)