convert char to numeric - 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

General SQL Server Forums

New to SQL Server Programming

convert char to numeric

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-01 : 22:22:24

I have one table and this field is character field with save data in below.

Bonus_table->bonus_amt_field. Char(20)
======================================
Record information
0
Null
Blank
3
4
Null
Blank

if i want to convert this character field => change to numeric field to display ,how to handle "Blank" and "null" record ? anyone can help give this sql query statement ?

The result expect:
0
0
0
3
4
0
0

I try this query but wrong message :

select cast(convert(numeric,3)bonus_amt) as bonus_amt
from test

Regards

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-02 : 07:37:58

Try this.

SELECT CAST(ISNULL(NULLIF(bonus_amt_field, ''), 0) AS int) FROM Test

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-01-03 : 12:57:22

or

select cast(case when bonus_amt is null or bonus_amt='' then 0 else bonus_amt end as numeric(3)) as bonus_amt from test

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources