To get rid of characters - 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

SQL Server 2005 Forums

Transact-SQL (2005)

To get rid of characters

Author

Topic

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2009-05-28 : 09:07:13

Hi,

I need to send for instance @ProductChecked='1,2' to my procedure. This procedure must make a control if a product having 1 or 2 exist.

In normal case you write select * from table where productid in (1,2) to make the control. But there will be an error in conversion if I do this by sending '1,2' because it will not be happy to find a string '1,2' as a productid which is an int. So I thought you could get rid of the beginning and ending character '. How is this done?

The procedure is something like this:

CREATE PROCEDURE spProcedureName
@ProductChecked varchar(100)=null
...
select column from table where producId in (@ProductChecked)

This select statement will return an error because of the beginning and ending character '. How can a retrieve '1,2,3,4' in my procedure and then only use these id's without the beginning and ending character?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 09:10:25

[code]
select column
from table
where productid in (select numval from CSVTable(@ProductChecked))
[/code]
get CSVTable here

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 09:13:22

select t.column from table as t
inner join dbo.fnparseList(',', @ProductChecked) AS x ON x.Data = t.ProductID

fnparseList is found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.63"
N 56°04'39.26"

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2009-05-28 : 09:14:47

Thanks a lot, both of you! :)

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2009-05-28 : 10:41:59

Hi guys, if anybody else happens to face this problem, here is an easy solution. The code below will just retrieve the data needed. You could of course make it more complex, but for me this will do.


DECLARE @String varchar(50)
SET @String = '1,2'

DECLARE @SQL VarChar(500) 
SELECT @SQL = ' SELECT Column from Table where Productid in (' + @String + ')'
EXEC (@SQL)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-28 : 10:47:36

Search for Array+SQL Server in google

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 11:04:55

quote:

Originally posted by Kurmanc

DECLARE @String varchar(50)
SET @String = '1,2'

DECLARE @SQL VarChar(500) 
SELECT @SQL = ' SELECT Column from Table where Productid in (' + @String + ')'
EXEC (@SQL)

And hope noone ever consider SQL injection...

E 12°55'05.63"
N 56°04'39.26"

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2009-05-28 : 11:23:27

Peso, thanks for the observation. Perhaps the functions suggested are something to consider instead.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 11:47:42

quote:
Originally posted by Kurmanc

Hi guys, if anybody else happens to face this problem, here is an easy solution. The code below will just retrieve the data needed. You could of course make it more complex, but for me this will do.

DECLARE @String varchar(50)
SET @String = '1,2'

DECLARE @SQL VarChar(500)
SELECT @SQL = ' SELECT Column from Table where Productid in (' + @String + ')'
EXEC (@SQL)

you could avoid the dynamic sql like this


SELECT Column from Table where ',' + @String + ',' LIKE '%,' + CAST(Productid  as varchar(10))+ ',%'

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2009-05-28 : 12:26:02

Works great visakh16, thanks a lot friend!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 12:35:01

No problem...you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources