how to convert varchar to int in sql..

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 2008 Forums

Transact-SQL (2008)

how to convert varchar to int in sql..

Author

Topic

sabareeswari
Starting Member

1 Post

Posted - 2012-12-14 : 00:07:07

sql query
select AuditCategoryName from IS_AuditCategory where AuditCategoryId in(select CheckListCategoryIds from IS_AuditChecklistProjectScope where IS_AuditChecklistProjectScope.[PrimaryProjectScopeId ] =2)

Error---
Conversion failed when converting the varchar value '46,48,43,45,44' to data type int.

sabari

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-14 : 00:39:59

Try this...


select AuditCategoryName 
from IS_AuditCategory t1
JOIN (select CheckListCategoryIds 
		from IS_AuditChecklistProjectScope 
		where IS_AuditChecklistProjectScope.[PrimaryProjectScopeId]=2
	  )t2
ON ','+CheckListCategoryIds+',' LIKE '%,'+CAST(AuditCategoryId as varchar(30))+',%'

Let me know the result

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 02:32:37

or use a string parsing UDF to get values onto a table and then join to that


DECLARE @CategoryIDs varchar(8000)

select @CategoryIDs=CheckListCategoryIds 
		from IS_AuditChecklistProjectScope 
		where IS_AuditChecklistProjectScope.[PrimaryProjectScopeId]=2

select AuditCategoryName 
from IS_AuditCategory t1
JOIN dbo.ParseValues(@CategoryIDs,',') f
ON f.Val = t1.AuditCategoryId 


ParseValues can be found in below link

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources