CODEWITHSUNDEEP
sqlsql-server
Gary Evans
Gary Evans

Reputation: 1880

converted data type (varchar) still shows conversion error

I have a column that can store text but is used to store a number (I did not make the system!) someone has put a blank value in (i.e. not content but not null) and its causing error: -

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

I have reduced the issue down to the below: -

SELECT 
    T1.[FIELD_5], 
    ISNUMERIC(T1.[FIELD_5]), 
    NULLIF(T1.[FIELD_5],''),
    ISNULL(NULLIF(T1.[FIELD_5],''),0),
    CONVERT(DECIMAL(18,5),ISNULL(NULLIF(T1.[FIELD_5],''),0))
FROM  
    [MyTBL] T1 
ORDER BY
    ISNUMERIC(T1.[FIELD_5])

The issue data is in [FIELD_5]

  1. I can see SQL sees a value as not numeric
  2. I can see that NULLIF is successfully changing it to a NULL value
  3. I can see the ISNULL is turning the NULLIF result to 0

But the CONVERT on the ISNULL result results in the error message, I would expect it to result in 0.00000

Upvotes: 0

Views: 101

Answers (3)

Gary Evans
Gary Evans

Reputation: 1880

This was a case of better investigation was needed, I should have realised as in my opinion SQL doesn't lie its normally always user error.

I run it again without the order by clause and then selected the row that would have shown up after the last row that did show up (i.e. that row that caused the error).

[FIELD_5] contained the value 1E-07, an infamous bad import from Excel!

What doesn't add up is why when I had the order by ISNUMERIC on, I did not see this value at the top of the list, only the blank values that were indeed being managed properly.

Question solved, I should have stuck investigating but I think this is worth leaving up to help other investigate in the future.

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

There is some non numeric value available you can do that check with case as below:

select convert(decimal(18,5), '')

Throws error as "Error converting data type varchar to numeric. "

SELECT 
    T1.[FIELD_5], 
    ISNUMERIC(T1.[FIELD_5]), 
    NULLIF(T1.[FIELD_5],''),
    ISNULL(NULLIF(T1.[FIELD_5],''),0),
    CONVERT(DECIMAL(18,5), iif(isnumeric(ISNULL(T1.[FIELD_5]),'0') > 1,T1.[FIELD_5],'0')
    ISNULL(NULLIF(T1.[FIELD_5],''),0))
FROM  
    [MyTBL] T1 
ORDER BY
    ISNUMERIC(T1.[FIELD_5])

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use try_convert():

SELECT T1.[FIELD_5], ISNUMERIC(T1.[FIELD_5]), NULLIF(T1.[FIELD_5], ''),
       COALESCE(NULLIF(T1.[FIELD_5], ''), 0),
       TRY_CONVERT(DECIMAL(18, 5), COALESCE(NULLIF(T1.[FIELD_5], ''), 0))
FROM [MyTBL] T1 
ORDER BY ISNUMERIC(T1.[FIELD_5]);

try_convert() was introduced in SQL Server 2012. If you are using an earlier version, then you need to use a case expression.

(I switched ISNULL() to COALESCE() because I prefer to use ANSI standard functions where practical.)

Upvotes: 2

Related Questions