Convert varchar to int... - 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 2000 Forums

Transact-SQL (2000)

Convert varchar to int...

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-11 : 14:50:15

I have tried CAST, CONVERT...nothing works...How else can I do this?!? Thanks alot

@EventIDs varchar(20),
SET @EventIDs = '4,5'

IF @EventIDs <> ''
BEGIN
UPDATE ProjectEvent
SET CompletedDate = GetDate()
WHERE ProjectID = @ProjectID
AND EventID IN(CONVERT(int, @EventIDs))--Sets CompletedDate
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-11 : 15:18:42

check this out (plus the comments)
http://www.sqlteam.com/item.asp?ItemID=11499

Be One with the Optimizer
TG

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-07-11 : 15:21:21

Just for fun, could you do something like


DECLARE @EventIDs VARCHAR(1000)
SET @EventIDs = '4,5'

SELECT
 CAST(RIGHT(LEFT(@EventIDs,Number-1)
 , CHARINDEX(',',REVERSE(LEFT(','+@EventIDs,Number-1)))) AS CHAR(30))
FROM 
 master..spt_values
WHERE
 Type = 'P' AND Number BETWEEN 1 AND LEN(@EventIDs)+1
 AND
 (SUBSTRING(@EventIDs,Number,1) = ',' OR SUBSTRING(@EventIDs,Number,1)  = '')

                               
------------------------------ 
4                             
5                             

(2 row(s) affected)

and incorporate this in your IN clause.

But you'd better read: http://www.sommarskog.se/arrays-in-sql.html

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources