Search code examples
sqlsql-serverisnumeric

Varchar to Numeric conversion - CLR or 'e0'

I want to convert data from Varchar column to Numeric data type -
so before conversion I am calling ISNUMERIC function to check whether data is numeric and if yes convert it to numeric data type. But I am facing an issue -
IsNumeric function is not behaving as expected - 

- IsNumeric(x) returns true - when varchar value has both comma and a tab character (Char(9)), 
- IsNumeric(x) returns false - when varchar value has only tab character and no comma

It is explained with the help of below SQL - 

DECLARE @propValue AS VARCHAR(50)
SET @propValue = '1,592 ' -- contains comma + tab (Char(9))
SELECT ISNUMERIC(@propValue)  -- Returns 1 

--If ISNUMERIC func returns true for this value, lets convert this Varchar value to Numeric
SELECT CAST(@propValue AS Numeric(19, 4)) -- :-( Error converting data type varchar to numeric.

I Googled and found various solutions to tackle this problem - 

--Solution 1: use 'e0'
SELECT ISNUMERIC(@propValue + 'e0')  -- Returns 0

--Solution 2: remove comma before calling IsNumeric()
SELECT ISNUMERIC(REPLACE(@propValue, ',', ''))  -- Returns 0

--Solution 3
--Call CLR function to parse Varchar value

What is the recommended solution in above scenario and why?
Also, I would really appreciate if anyone can explain why IsNumeric(x) returns false - when varchar value has only tab character and no comma?

Thank you!

Solution

  • Keep in mind that ISNUMERIC() = 1 does not mean "can be converted to every numeric type" but rather "can be converted to at least one numeric type." This comes up a lot where the value can be converted to at least one numeric type, but not the one you want. A much more basic example:

    IF ISNUMERIC(256) = 1
  SELECT CONVERT(TINYINT, 256);

    If you have strings that have known violations (such as tabs), then why not also replace tabs? Why not prevent garbage data from getting into this value in the first place?

    This works but it is quite ugly:

    DECLARE @x TABLE (propValue VARCHAR(50));

INSERT @x SELECT '1,592' + CHAR(9)
UNION ALL SELECT '55' + CHAR(9) + '32'
UNION ALL SELECT CHAR(9) + '7,452.32    '
UNION ALL SELECT 'foo'
UNION ALL SELECT '74';

SELECT CONVERT(NUMERIC(19,4), 
LTRIM(RTRIM(REPLACE(REPLACE(propValue, CHAR(9), ''), ',', '')))
)
FROM @x
WHERE ISNUMERIC(LTRIM(RTRIM(REPLACE(REPLACE(propValue, 
  CHAR(9), ''), ',', ''))) + 'e0') = 1;

    While not applicable to your exact question, I wrote a FAQ about this 10 years ago: http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html