>>BTW why you need Numeric(20)?
>>Wouldn't BIGINT work for you?
>>THAT is 20 bytes for record! BigInt is only 8 bytes and it is 2^63?
>>But you know your data better :-)
>>
>>DECLARE @Test TABLE (Fld1 varchar(200), fld2 numeric(10))
>>INSERT INTO @Test VALUES ('1',0)
>>INSERT INTO @Test VALUES ('1e1',0)
>>INSERT INTO @Test VALUES ('1d12',0)
>>INSERT INTO @Test VALUES ('',0)
>>
>>UPDATE @Test SET Fld2 = CAST(Fld1 as int)
>>WHERE ISNUMERIC(Fld1+'0e1') = 1
>>
>>SELECT * FROM @Test
>>
>
>It the data would have been numeric entirely, yes, what you just mentioned would have been better. Thanks for letting me know.
I am not sure you (or anybody else) notice the changes I made in ISNUMEIRC() function:
From:
ISNUMERIC(Fld1+'e1') = 1
to
ISNUMERIC(Fld1+'0e1') = 1
That is a trick I learned from George Mastros. Unfortunately he only post in tek-tips forum.
The goal here is that IF you have values as in the example above ISNUMERIC(Fld1+'0e1') returns:
'1' -> '1e1' -> 1
'1e1' -> '1e1e1' -> 0
'1d12'-> '1d12e1'-> 0
'' -> 'e1' -> 1
and you update only these records where you have a proper numeric values :-)
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.