Change a database column type while ignoring conversion errors using SQL

Question

This is similar to a previous question MSSQL: Change type of a column with numbers from varchar to int, except in this case not all of the original data can be successfully converted from varchar to int. In these cases, I want the output to just be NULL.

For example, the incoming set of values in a varchar column will look like: {'123', '234', '345', 'A', '456'}. After conversion from varchar to int, I want the column to contain the values {123, 234, 345, NULL, 456}. In practice, I'll have about 99% success rate.

While a general solution would be ideal, I am using SQL Server 2005 and need to perform the change in Transact SQL.

EDIT: The IsNumeric suggestions do bridge a portion of the gap, but are not complete. For example, if the data set includes numeric but not integer data, the IsNumeric fix doesn't work (and there doesn't appear to be a IsInteger). In particular, the following set will not convert {'123.456', '$12'} where they are both numeric according to SQL Server IsNumeric but will not convert to Int.

Answer 1

Will IsNumeric function help in this case?

Answer 2

1. Run an update thus:

   .. SET OldValue = CASE WHEN ISNUMERIC(Oldvalue) = 1 THEN Oldvalue ELSE NULL END ...

2. Run

   ALTER TABLE dbo.Mytable ALTER COLUMN OldValue int --Add NULL or NOT NULL as needed

Answer 3

1. Execute this first:

   update table set field = null where ISNUMERIC(field) = 0

2. Then change the type

Answer 4

After an hour of hacking around and with guidance from other answers, here's what I've come up with:

1. Clean up the data to ensure that information will convert. Since there is no IsInteger function, use IsNumeric to set to NULL anything that can't convert at all.

2. Convert all remaining data to type MONEY which will effectively get rid of the currency symbols that were previously accepted by IsNumeric.

3. Convert the money type to an integer to get rid of the fraction.

4. Finally, change the data type.

The following test demonstrates using SQL:

CREATE TABLE tbl ( 
    id INTEGER IDENTITY NOT NULL,
    col VARCHAR(20)
)

INSERT INTO tbl (col) VALUES ('123')
INSERT INTO tbl (col) VALUES ('ABC')
INSERT INTO tbl (col) VALUES ('123.456')
INSERT INTO tbl (col) VALUES ('$12')

UPDATE tbl SET col = CASE WHEN IsNumeric(col) = 1 THEN Convert(INTEGER, (Convert(MONEY, col))) ELSE NULL END
ALTER TABLE tbl ALTER COLUMN col INTEGER

SELECT * FROM tbl