cast and try catch - 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)

cast and try catch

Author

Topic

aykutucar
Starting Member

5 Posts

Posted - 2008-01-14 : 14:42:56

Hi,
Is it possible to cast a varchar to int and if it is not a successful cast return 0 ?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-01-14 : 14:58:48

Sort of.

declare @tst varchar(10)
set @tst = '123'
select case isnumeric(@tst) when 1 then @tst else 0 end as CheckTest
set @tst = 'a123'
select case isnumeric(@tst) when 1 then @tst else 0 end as CheckTest

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-14 : 15:18:45

quote:
Originally posted by aykutucar

Hi,
Is it possible to cast a varchar to int and if it is not a successful cast return 0 ?

yes, did you try?
You can certainly perform a CAST within a Try/Catch block. You just can't use Try/Catch in a user defined function.

Be One with the Optimizer
TG

aykutucar
Starting Member

5 Posts

Posted - 2008-01-14 : 19:17:20

Thank you jhocutt.It worked.
It is irrelavent but do you know why I get Error
When I say
CASE ISNUMERIC([RFMNUM]) WHEN 1 THEN CAST([RFMNUM] AS FLOAT) ELSE 0 END >=2
The problem is FLOAT.If I say INT instead of it, no problem.
I also tried the NUMERIC(18,0) but I get 'can not convert varchar to Numeric' Error
Regards

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-15 : 02:52:11

Try specifying the desired precision and scale on your decimal/numeric
declaration , such as :
CASE ISNUMERIC([RFMNUM]) WHEN 1 THEN CAST([RFMNUM] AS DECIMAL(10,2)) ELSE 0 END >=2

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 02:18:22

Note that isnumeric() is not reliable

select data from
(
select '23487' as data union all
select '12d3' union all
select '$'
) as t
where isnumeric(data)=1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources