Help needed using Convert VarChar to Int

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)

Help needed using Convert VarChar to Int

Author

Topic

Rimsky
Starting Member

12 Posts

Posted - 2007-06-12 : 11:14:52

I have a dataset which needs converting from VarChar to Int. On first inspections values seemed to be 64000, 64051, etc. Upon running the query I found values like '64SEAF'. This fails my converting it.
It boils down to this:
DECLARE @n VarChar(100)
DECLARE @intResult Int
SET @n = '64SEAF'
SET @intResult = CONVERT(Int, @n)
print @intResult

This gives me:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '64SEAF' to data type int.

How do I effectively convert the VarChar '64SEAF' to an Int. The result would preferably be 64.

Tnx for any insights.

Cees Cappelle

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 11:17:10

Extract only numbers
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56713

Madhivanan

Failing to plan is Planning to fail

Rimsky
Starting Member

12 Posts

Posted - 2007-06-12 : 12:03:21

Thank you for the tip. That'll work.

Cees

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-12 : 12:04:54

What do you want to do with '64SEAF24'?
If you want 64 from that then it's
left(str, patindex('%[^0-9]%',str)-1)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources