[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:
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:
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.
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
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, '.'))
- It is supported in SQL Server 2008 or above.
- Money range is : -922,337,203,685,477.5808 to 922,337,203,685,477.5807 - four decimals.
More Related questions