Adam Haines (2/19/2008)
--------------------------------------------------------------------------------
DECLARE @IDString VARCHAR(MAX)SET @IDString =(SELECT (ID) + ','FROM MYTABLE--WHERE FILTER CAN GO HEREFOR XML PATH(''))

This leaves a trail comma. I usually do something likeDECLARE @IDString VARCHAR(MAX)SET @IDString =(SELECT CASE row_number() OVER(ORDER BY ID) WHEN 1 THEN '' ELSE ',' END + (ID)FROM MYTABLE--WHERE FILTER CAN GO HEREFOR XML PATH(''))

GSquared (3/3/2008)

---

Just did some tests.

The XML version is faster, significantly so, than the Numbers table version, for parsing out a string.

Jeff Moden (3/31/2008)

---

Any bets? ;) Post your test data please, the Numbers Table code you used for the split, and the XML code you used for the split and we'll see :)

arshad7887 (8/20/2011)

---

To Run this kind of query first you need to create UDF(user defined function)

SPLIT Varchar in sql server

User Defined Method

CREATE FUNCTION SplitString
(
-- Add the parameters for the function here
@myString varchar(500),
@deliminator varchar(10)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [varchar](50) NULL
)
AS
BEGIN
Declare @iSpaces int
Declare @part varchar(50)

--initialize spaces
Select @iSpaces = charindex(@deliminator,@myString,0)
While @iSpaces > 0

Begin
Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

Insert Into @ReturnTable(part)
Select @part

Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))


Select @iSpaces = charindex(@deliminator,@myString,0)
end

If len(@myString) > 0
Insert Into @ReturnTable
Select @myString

RETURN
END
GO

Now Run this query
RUN The query
select * From SplitString('Mohammed**Arshad**Shaikh','**')

You may use it in where clause also.

Declare @Paramlist varchar(50)
Set @Paramlist = '1,2,3'

SELECT * FROM Customer
WHERE CUSTOMERId In(select * From SplitString(@paramlist,','))

This will work sure.
Insha Allah..