CODEWITHSUNDEEP
sqlsql-servert-sql
Kyle B.
Kyle B.

Reputation: 5787

SQL IsNumeric Returns True but SQL Reports 'Conversion Failed'

Assuming the following data:

Column1 (data type: varchar(50))
--------
11.6
-1
1,000
10"    
Non-Numeric String

I have a query, which is pulling data from this column and would like to determine if the value is a number, then return it as such in my query. So I am doing the following

SELECT CASE
       WHEN IsNumeric(Replace(Column1, '"', '')) = 1 THEN Replace(Column1, '"', '')
       ELSE 0
   END AS NumericValue

SQL is reporting back:

Conversion failed when converting the varchar value '11.6' to data type int.

Why? I have also tried to force cast this:

SELECT CASE
       WHEN IsNumeric(Replace(Column1, '"', '')) = 1 THEN cast(Replace(Column1, '"', '') AS float)
       ELSE 0
   END AS NumericValue

And I got:

Error converting data type varchar to float.

Upvotes: 13

Views: 25705

Answers (8)

Polina F.
Polina F.

Reputation: 649

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

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

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

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

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

Upvotes: 6

Hung Doan
Hung Doan

Reputation: 1167

I have just meet this issue.

You can try this solution if you don't mind about limitation 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.

Upvotes: 0

user2284133
user2284133

Reputation: 1

This solution does not work in all cases (specifically numbers with money and/or thousand separators). Concatenate an exponent representation to the end of the number which is represented by a string...ISNUMERIC() works 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 the very least, standardizes the format for using the REPLACE() function.

Upvotes: 0

user489390
user489390

Reputation: 11

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

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425251

You need to replace comma with a period:

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

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

Upvotes: 11

brendan
brendan

Reputation: 29976

There are many issues with SQL isnumeric. For example:

select isnumeric('1e5')

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

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

Upvotes: 5

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

Kyle,

I think this solves the problem. The problem lies in the fact that the ELSE clause initializes your result to be an INTEGER. By making an explicit typecast to FLOAT and adding the suggestion of Quassnoi, it seems 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

Upvotes: 1

dotjoe
dotjoe

Reputation: 26940

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

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

Upvotes: 1

Related Questions