[ACCEPTED]-SQL IsNumeric Returns True but SQL Reports 'Conversion Failed'-tsql

You need to replace comma with a period:

CAST(REPLACE(column, ',', '.') AS FLOAT)

SQL Server outputs 3 decimal separator defined with locale, but 2 does not unterstand anything but a period 1 in CASTs to numeric types.

First convert the string to money, then 13 covert it to any other numeric format since 12 money type gives a true numeric string always. You 11 will never see an error then.

Try the following 10 in your query, and you'll know what I am 9 talking about. Both will return 2345.5656. The 8 Money datatype is rounded to 4 decimal places, and 7 hence the casting causes rounding to 4 decimal 6 places.

SELECT CAST('2,345.56556' as money), CAST('$2,345.56556' as money)

Cast( cast('2,344' as money) as float) will 5 work perfectly or cast( cast('2,344' as 4 money) as decimal(7,2)) will also work.

Even 3 cast(CAST('$2,345.56556' as money) as int 2 ) will work perfectly rounding it to nearest 1 integer.

There are many issues with SQL isnumeric. For 5 example:

select isnumeric('1e5')

This will return 1 but in many languages 4 if you try to convert it to a number it 3 will fail. A better approach is to create 2 your own user defined function with the 1 parameters you need to check for:

http://www.tek-tips.com/faqs.cfm?fid=6423

ISNUMERIC returns 1 when the input expression 4 evaluates to a valid integer, floating point 3 number, money or decimal type;

So the problem 2 is it is a valid number but not a valid 1 int.

Kyle,

I think this solves the problem. The 5 problem lies in the fact that the ELSE clause 4 initializes your result to be an INTEGER. By 3 making an explicit typecast to FLOAT and 2 adding the suggestion of Quassnoi, it seems 1 to work.

DECLARE @MyTable TABLE (Column1 VARCHAR(50))
INSERT INTO @MyTable VALUES('11.6')
INSERT INTO @MyTable VALUES('-1')
INSERT INTO @MyTable VALUES('1,000')
INSERT INTO @MyTable VALUES('10"    ')
INSERT INTO @MyTable VALUES('Non-Numeric String')

SELECT CASE WHEN ISNUMERIC(REPLACE(Column1,'"','')) = 1 THEN REPLACE(REPLACE(Column1,'"',''), ',', '.') ELSE CAST(0 AS FLOAT) END
FROM @MyTable

Regards,
Lieven

IsNumeric(' ') also returns 1, but then CAST as int blows 2 up. Brendan above says write your own function. He 1 is correct.

This solution does not work in all cases 7 (specifically numbers with money and/or 6 thousand separators). Concatenate an exponent 5 representation to the end of the number 4 which is represented by a string...ISNUMERIC() works 3 fine from there. Examples below:

-- CURRENT ISNUMERIC RESULTS
SELECT ISNUMERIC('11.6'); --1
SELECT ISNUMERIC ('-1'); --1
SELECT ISNUMERIC('1,000'); --1
SELECT ISNUMERIC('10"'); --0
SELECT ISNUMERIC('$10'); --1

-- NEW ISNUMERIC RESULTS
SELECT ISNUMERIC('11.6'+'e+00'); --1
SELECT ISNUMERIC ('-1'+'e+00'); --1
SELECT ISNUMERIC('1,000'+'e+00'); --0
SELECT ISNUMERIC('10"'+'e+00'); --0
SELECT ISNUMERIC('$10'+'e+00'); --0

This, at 2 the very least, standardizes the format 1 for using the REPLACE() function.

I have just meet this issue.

You can try 2 this solution if you don't mind about limitation 1 of decimal length.

CONVERT(numeric, CONVERT(money, '.')) 

NOTE:

1. It is supported in SQL Server 2008 or above.
2. Money range is : -922,337,203,685,477.5808 to 922,337,203,685,477.5807 - four decimals.