2

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!

benRollag
  • 1,219
  • 4
  • 16
  • 21
inutan
  • 10,558
  • 27
  • 84
  • 126

2 Answers2

1

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

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks for your reply Aaron, well yeah this is one of the option. Can you suggest a good way in which I can just specify white list (probably 0-9 characters and a decimal) instead of worrying about long black list. Is CLR a good option for that? Any other? – inutan Jul 05 '12 at 14:04
  • @iniki with a whitelist only, you would simply have to either reject any values that contain characters outside of your whitelist, or build the string a character at a time based on adherence to your whitelist (this also gets complex because if you allow, say, negative values, then is `'55-62'` a valid value?). What is your goal? To reject bad values or try to convert them at all costs? The proper answer is to stop storing numbers as strings in the first place... – Aaron Bertrand Jul 05 '12 at 14:07
  • Thanks for your reply Aaron, incoming data is not in our control for now :-( so have to find a way to deal with invalid numeric chars. I have posted another question @ http://stackoverflow.com/questions/11348187/using-patindex-instead-of-isnumeric-with-whitelist plz see if you could help. Thank you! – inutan Jul 05 '12 at 16:01
-1
ISNUMERIC(RTRIM(LTRIM(x))) = 1
Russell Fox
  • 5,273
  • 1
  • 24
  • 28