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