Split on Comma to use with an IN statement

[davism]

Hi all,

How can I split information on a comma and put use for an IN statement?

For example, I want to exclude certain states. Now, I want to take the variable that is passed in and put it in a temporary table then use the temporary table in a NOT IN (select * from #States) statement.

How can I split the information variable to put in the temporary table? Here is some code on INSERTing. But the Values (@TEST) is putting the whole thing in but I want it split.

CREATE TABLE #States
(
strState varchar(256)
)

INSERT INTO #States
Values (@Test)

Select * from #States

How can I do this?

It's SQL 2000 by the way.

Thanks much for any assistance you can provide.

 

[bborissov]

Search here in FAQ for Split function.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[davism]

You're right.

Thanks.