where ','+@var+',' like '%,'+field+',%'

DECLARE @strText VARCHAR(1000)
SET @strText = 'one,two,three,four,five'

-- Makes sure there is a trailing comma, or else it won't work properly
SET @strText = @strText + CASE WHEN RIGHT(@strText,1) <> ',' THEN ',' ELSE '' END

CREATE TABLE #HoldingTable (Item VARCHAR(50))

WHILE LEN(@strText) > 0
BEGIN
	INSERT INTO #HoldingTable
	-- extracts characters up until the first comma and pops it into a temp table
		SELECT LEFT(@strText,CHARINDEX(',',@strText)-1) as Item
	--removes same text from @strText now it has been inserted into temp table
	SET @strText = REPLACE(@strText,LEFT(@strText,CHARINDEX(',',@strText)),'')
END

SELECT Item FROM #HoldingTable

DECLARE @strOutput VARCHAR(1000)
SET @strOutput = ''

SELECT @strOutput = @strOutput + Item + ', ' 
	FROM (SELECT Item FROM #HoldingTable) x

-- Uses case statement to handle an empty table
SELECT CASE WHEN LEN(@strOutput) > 0 THEN LEFT(@strOutput,LEN(@strOutput)-1) ELSE @strOutput END as CSVString

DROP TABLE #HoldingTable

CREATE FUNCTION dbo.fnParseList
(
    @Delimiter CHAR,
    @Text varchar(max)
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
    DECLARE @NextPos INT,
        @LastPos INT

    SELECT  @NextPos = CHARINDEX(@Delimiter, @Text, 1),
        @LastPos = 0

    WHILE @NextPos > 0
        BEGIN
            INSERT  @Result
                (
                    Data
                )
            SELECT  SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)

            SELECT  @LastPos = @NextPos,
                @NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
        END

    IF @NextPos <= @LastPos
        INSERT  @Result
            (
                Data
            )
        SELECT  SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)

    RETURN
END
select * from dbo.fnParseList(',','0976-009,3455-076,1234-123,2345-678,9999-878,2222-555')

declare @var varchar(max);
set @var = '0976-009,3455-076,1234-123,2345-678,9999-878,2222-555';

declare @xml xml;
select @xml =  
	cast('<r>'+replace(
		(select @var for xml path ('')) ,',','</r><r>')+'</r>' 
	as xml)

select c1.value('(./text())[1]','NVARCHAR(50)') from 
	@xml.nodes('/r') T(c1);

quote:

---

Originally posted by Lamprey

Everything you ever wanted to know about using Arrays in SQL:
http://www.sommarskog.se/arrays-in-sql.html

---

quote:

---

Originally posted by LopakaB

been searching for the answer but...
I have created a variable @var = 'a, b, c, d'
i am trying to use it in a select:
Select *
From Table
Where field IN @var

Keep getting errors... any help would be appreciated...
Thanks...

Lopaka

---