System conversions always pose challenges. Mapping fields from the old system to the new system is challenging enough even before you factor in the necessary data type conversions. It seems every "old" system has repurposed fields. You know what I mean. That numeric value stored in that text field means you now have to code around non-numeric values in your conversion logic.

SQL Server's ISNUMERIC() function helps that effort. ISNUMERIC() accepts a text string as an input and returns a Boolean result depending on whether or not SQL Server believes the input text string could be converted to a numeric value. SELECT ISNUMERIC('1' ) returns TRUE. SELECT ISNUMERIC('A') returns FALSE. The logic seems simple enough. One example of handling non-numeric values so your data conversion does not fail includes:

 DECLARE @NumericString VARCHAR(10)
 SET @NumericString = 'A'
 SELECT 
 CASE
 WHEN ISNUMERIC(@NumericString) = 1
 THEN CONVERT(DECIMAL(8,3),@NumericString)
 ELSE NULL
 END

Properly using ISNUMERIC() in your data conversion code means no more data conversion errors, right?

Not quite. Try SELECT ISNUMERIC('1,1') and SELECT CONVERT(DECIMAL(8,3), '1,1'). The first select returns TRUE. The second select returns "Error converting data type varchar to numeric." How could SQL Server decide the same text string is both numeric and unable to convert to numeric?

With a little testing, I found several scenarios where ISNUMERIC() returns TRUE, but CONVERT() errors. The following table shows the results of my testing. The inconsistencies are called out in Italics.

Text String

ISNUMERIC(?)

CONVERT(DECIMAL(8,3),?)

'1'

TRUE

1.000

'A'

FALSE

ERROR

'1.1'

TRUE

1.100

'1,1'

TRUE

ERROR

'1,100'

TRUE

ERROR

' 1'

TRUE

1.000

'1 '

TRUE

1.000

'1 1'

FALSE

ERROR

'$1'

TRUE

ERROR

'#1'

FALSE

ERROR

'1%'

FALSE

ERROR

'+1'

TRUE

1.000

'-1'

TRUE

-1.000


Notice a comma anywhere in the text string causes an error even if the placement of that column makes the string look like a properly formatted numeric value. Also notice a dollar sign is allowed in ISNUMERIC(), but fails in CONVERT().

The test results in my table are likely not inclusive of all the possible combinations of characters that could cause problems in your data conversion efforts. My sample tests just reflect some of the more common examples I have seen.

How do you get around those pesky ISNUMERIC() versus CONVERT() inconsistencies? I used CONVERT(DECIMAL(8,3),REPLACE('$1','$','')) to remove that dollar sign before successfully converting the text string to a decimal value.

In case you are wondering, I also compared the CAST() function against ISNUMERIC(). In my tests, both CAST() and CONVERT() threw the same errors relative to ISNUMERIC().

So the next time you convert data and receive data type conversion errors despite your proper placement of the ISNUMERIC() function, consider some of SQL Server's apparent inconsistencies.

Maybe someone should run ISDATE() through its paces to see what sort of inconsistencies are returned.