October 27, 2011 at 8:04 am
Hi all,
I'm trying to execute the following statement.
update table1
set col1 = case when isnumeric(col2) = 1 then col2 end
col1 and col2 are in the same table and have varchar(max) data type.
I'm getting the following error
Arithmetic overflow error converting varchar to data type numeric.
Can some one help me on this.
October 27, 2011 at 8:31 am
try using this
update table1
set co1 = case when isnumeric(cast(col2 AS nvarchar))= 1 then col2 end
October 27, 2011 at 8:32 am
small correction
col1 is data type decimal(5,2)
col2 is varchar of max
October 27, 2011 at 8:33 am
if previous does not work, try this...
update table1
set co1 = case when isnumeric(coalesce(cast(col2 AS nvarchar),'x'))= 1 then col2 end
October 27, 2011 at 8:41 am
i'm still getting the error.
October 27, 2011 at 8:52 am
col2 must be containing abnormal values that SQL server has trouble in casting.
try identifying.
you may use this if there aren't many non-numeirc rows
select distinct col2 from table1 where isnumeric(col2) <> 1
check for funny looking characters or invisible values on the result set and let us know what you find out
October 27, 2011 at 9:07 am
in col2 I have values
col2
-----
+
00100
230
-0.1
ch
when i do a select
select case when ISNUMERIC(col2) = 1 then col2 from table1
result set
col2
----
+
00100
230
-0.1
NULL
then i perform the update
update table1
set col1 = CASE WHEN ISNUMERIC(col2) = 1 THEN col2 END
it fails
October 27, 2011 at 9:20 am
'+' is causing you trouble. isnumeric returns true when '+' is supplied as an argument which then fails to convert it to numeric.
October 27, 2011 at 9:23 am
can you do a case on the set line of an update? never tried it myself
update table1
set col1 = col2
where col2 in ( select case when ISNUMERIC(col2) = 1 THEN col2 END) and col2 <> '+'
October 27, 2011 at 9:43 am
I strongly suggest that you read this item by Jeff Moden
Why doesn’t ISNUMERIC work correctly? (SQL Spackle)
By Jeff Moden, 2010/12/01
http://www.sqlservercentral.com/articles/IsNumeric/71512/
Jeff lists numerous reasons, with examples, of why ISNUMERIC "fails" to perform as anticipated.
October 27, 2011 at 9:43 am
since the 'where' clause is evaluated after the 'select' clause, it will still not help. ANDing isnumeric() with col2 NOT IN ('+','-') would help.
update table1
set col1 = case when isnumeric(col2) = 1 AND col2 NOT IN ('+','-') then col2 end
October 27, 2011 at 9:44 am
since the 'where' clause is evaluated after the 'select' clause, it will still not help. ANDing isnumeric() with col2 NOT IN ('+','-') would help.
update table1
set col1 = case when isnumeric(col2) = 1 AND col2 NOT IN ('+','-') then col2 end
October 27, 2011 at 10:56 am
kevin4u06 (10/27/2011)
small correctioncol1 is data type decimal(5,2)
col2 is varchar of max
Since col2 is a varchar column, it's best to treat it like a varchar and use pattern matching. Try this; for each record where col2 doesn't contain a character other than 0-9 or ".", update col1 with col2.
update table1
set col1 = col2
where col2 not like '%[^0-9.]%';
Adjust the LIKE pattern however you need to compensate for what should be considered "numeric" values.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 27, 2011 at 11:01 am
this is a smooth one.. unfortunately col2 has a value -0.1. So it will skip negative values.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply