CSV String - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CSV String

Author

Topic

mahimam_2004
Starting Member

40 Posts

Posted - 2006-12-14 : 22:02:58

Hi,

I have a variable like @states=NY,VA,AL,CA.

Now i want to use the above string in the IN clause.How to separate those string and use.

Select * from xxx where @State IN('NY','VA','AL','CA')

Thanks in advance

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-14 : 22:41:35

Cross post.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-14 : 23:20:07

Assuming that stateid is one of the columns, this may work

where '%,'+@States+',%' like '%,'+cast(stateid as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2006-12-15 : 00:00:19

Hi,

declare @state varchar(100),@st varchar(100)
set @state='NY,VA,AL,CA'

--select REPLACE ( @state , ',' , ''',''' )

select @st=''''+REPLACE ( @state , ',' , ''',''' )+''''
print @st

just try this

Madhu

Kristen
Test

22859 Posts

Posted - 2006-12-15 : 02:49:48

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions

mahimam_2004
Starting Member

40 Posts

Posted - 2006-12-15 : 09:23:18

Madhu Thank you very much.
It is working

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 10:59:24

Now, how will you use that to solve your problem?

Madhivanan

Failing to plan is Planning to fail

mahimam_2004
Starting Member

40 Posts

Posted - 2006-12-15 : 11:24:39

Now Iam using the @State in IN clause
Like select ID from Organization where Name IN(@State)
But it is not producing the result.Why?How to use the @state in 'IN'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 11:30:11

Read my first reply

Madhivanan

Failing to plan is Planning to fail

mahimam_2004
Starting Member

40 Posts

Posted - 2006-12-15 : 11:45:23

You gave the expression with regards to the @StateID.Thatswhy i didn't use that.
But I wrote a function like this:
Alter Function dbo.ListOfvalues(@values varchar(8000),@delimeter char) Returns @list table (Item nvarchar(4000))
as
Begin
While CharIndex(@delimeter,@values) >0
Begin
Insert Into @list
Select Ltrim(RTrim(Left(@values,CharIndex(@delimeter,@values)-1)))
Select @values=RTRIM(Ltrim(Right(@values,len(@values)-CharIndex(@delimeter,@values))))
End
Insert Into @list
Select @values
Return;
End

Declare @States nvarchar(2000)
Select * from adOrganization where name IN (Select item from dbo.ListOfvalues(@States,','))

The above function is working properly.In my report the user is selecting values with checkboxes from dropdown list,The report sending seelcted values to my procedure like this 'x,xx,xxx,xxxx' and i need to separate the result set append single quotes.That separated string i need to use in the IN clause.
thats why i need this functionality.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 22:17:01

<<
You gave the expression with regards to the @StateID.Thatswhy i didn't use that.
>>

The solution I provided is simple and no need of using split function
If you want to use name in place of stateid, then use it

Madhivanan

Failing to plan is Planning to fail

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2006-12-15 : 23:52:46

hI,
TRY THIS

uSE aDVENTUREwORKS
select *from person.stateprovince WHERE STATEPROVINCECODE IN('NY','VA','AL','CA')

declare @state varchar(50),@st VARchar(100)
set @state='NY,VA,AL,CA'
select @st=''''+REPLACE ( @state , ',' , ''',''' )+''''
print @st
Declare @SqlStatement nchar(2000)
DECLARE @SQL VARCHAR(200)
SET @SqlStatement= 'select *from person.stateprovince WHERE STATEPROVINCECODE IN ('+ @ST+')'
PRINT @SqlStatement
EXEC SP_EXECUTESQL @SqlStatement

Kristen
Test

22859 Posts

Posted - 2006-12-16 : 04:31:25

Yeah, that'll work, but it has lots of flaws, not least of which:

Slow - query plans unlikely to be cached.

Uses dynamic SQL and requires permissions on the underlying table

Why don't you just do what Madhi suggested? Or use a SPLIT function as per the link I posted?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-16 : 06:49:23

More info on Dynamic sql
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2006-12-17 : 13:18:26

Hi Kristen,

I hope the 'Execute As' Statement in SQL 2005 will address u r permission apprehension :).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-18 : 09:30:14

quote:
Originally posted by madhuotp

Hi Kristen,

I hope the 'Execute As' Statement in SQL 2005 will address u r permission apprehension :).

Well there is no need of using DYNAMIC SQL when it can be done without it

Madhivanan

Failing to plan is Planning to fail

mahimam_2004
Starting Member

40 Posts

Posted - 2006-12-18 : 12:06:12

Madhivanan,
I used your logic,Out of all it is so simple to use.
Thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources