I had no primary key on the table and no guarantee of uniqueness on any given row. Because I was using the NaN column as part of the where clause, I would get an overflow.

In order to null the values, I first had to create a unique column (identity)

-- add idenity column

Alter Table dbo.TABLE

Add UniqueID Int Identity(1, 1)

Go

Now I can take the raw data converted into NVarChar as well as the IsNumeric value and put it into a temporary table.

-- select data into temp table

SELECT

convert(nvarchar (20), column1) as column1

,convert(nvarchar (20), column2) as column2

,convert(nvarchar (20), column3) as column3

,isnumeric(column1) as col1_Numeric

,isnumeric(column2) as col2_Numeric

,isnumeric(column3) as col3_Numeric

,[UniqueID]

INTO [TEMPORARYTABLE]

FROM

order by col1_Numeric asc

I can update the original table by identity column and NULL the NaN values .

--null the NaN data in Column1

Update A

set A.Column1 = null

From TABLE A, TEMPORARYTABLE B

Where A.UniqueID=B.UniqueID

And B.col1_Numeric=0

--null the NAN data in Column2

Update A

set A.Column2 = null

From TABLE A, TEMPORARYTABLE B

Where A.UniqueID=B.UniqueID

And B.col2_Numeric=0

And clean up

--drop identity column

Alter Table TABLE

Drop Column UniqueID