Arithmetic overflow error converting varchar to da

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)

Arithmetic overflow error converting varchar to da

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2012-06-26 : 17:59:24

Good afernoon

i need your help

ive got this query that yilds me an error:

select CDR_NUMBER,B_NUMBER,convert(numeric(12,0) ,B_NUMBER) --as Alfanumerico
,case when isnumeric(B_NUMBER) <> 1 then 'Campo Alfanumerico' else 'Numerico' end from TEMP_ICT_DW

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.

i query this firts 243 rows since in row 243 yields that error:
select top 243 CDR_NUMBER,B_NUMBER from TEMP_ICT_DW

i found a data with a non numeric data

13693 03168723841
13694 03167431143
13694 03167431143
13695 03163801808
13695 03163801808
13696 +1433115167292

1.how do i remove non numeric data like +1433115167292 ?
2. after removing non numeric ie +1433115167292 to 1433115167292 what data type supports this data ?

Id appreciate your help

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-06-27 : 00:49:12

+1433115167292 can be converted as a number - but it requires at least numeric(13,0). If you have other non-numeric data add a filter to your where clause to eliminate the non-numeric values.

WHERE isnumeric(B_NUMBER) = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources