declare @myTab table( ExtRef varchar(9), IntRef decimal(38,19))
insert into @myTab(ExtRef,IntRef) values ('1',0)
insert into @myTab(ExtRef,IntRef) values ('2.1',0)
insert into @myTab(ExtRef,IntRef) values ('A',0)
update @myTab
set IntRef = case when isnumeric(ExtRef) = 1 then ExtRef else -1
--(A)
end

update @myTab
set IntRef = case when isnumeric(ExtRef) = 1 then ExtRef else -1
--(A)
end

JohnG (11/20/2008)

---

Additionally,

1. It doesn't properly support a legitimate value of -1 as -1 is being used to indicate that the value is not numeric.

2. An explicit CAST is needed in the assignment part ("THEN") of the CASE statement as for some quirky reason, a value of '123.45' cannot be implicitly converted to DECIMAL(38,19) when it is part of a CASE statement. Error: Conversion failed when converting the varchar value '123.45' to data type int. Yet a direct assignment (IntRef = ExtRef) works fine! (SQL Server 2005 SP2)