Error converting data type varchar to numeric when using CASE

Question

SELECT ID,
CASE WHEN ISNUMERIC(COL_VALUE) = 1 THEN CONVERT(NUMERIC, COL_VALUE) * 1000
  ELSE COL_VALUE 
END AS [COL_VALUE]
FROM Table

The original data type is varchar that is why I convert COL_VALUE to numeric. It seems like something wrong with ELSE statement, when I execute the query without ELSE statement the non-numeric value will become NULL. I check whether or not the column is numeric, if it is numeric then multiply by 1000, if not numeric then return original value. There are few non-numeric values like:

23`, 34/, 34=4.

so, according to your CASE expression, if the value of the column is numeric, you want to return a numeric data type, and if it's not numeric, to return another data type. A column has to be of a single data type — Lamak
– Lamak, Commented Jul 6, 2015 at 17:00
All of the paths in your CASE must resolve to the same type. If COL_VALUE has the possibility of being a VARCHAR, you need to make your first path return a VARCHAR as well, like, CAST(CONVERT(NUMERIC, COL_VALUE) * 1000) AS NVARCHAR) — Tom
– Tom, Commented Jul 6, 2015 at 17:01
As you are on 2012 you should use TRY_CONVERT instead of ISNUMERIC as well. — Martin Smith
– Martin Smith, Commented Jul 6, 2015 at 17:03

Giorgi Nakeuri · Accepted Answer · 2015-07-06 18:38:04Z

4

Since you are returning numeric and non umeric values you shoud cast everything back to varchar. Also use try_cast function because isnumeric function will return sometimes true when you have dollar sign in your string for example and convert function will fail:

SELECT ID,
       CASE WHEN TRY_CAST(COL_VALUE AS NUMERIC) IS NOT NULL 
            THEN CAST(CAST(COL_VALUE AS NUMERIC) * 1000 AS VARCHAR(100))
            ELSE COL_VALUE 
       END AS [COL_VALUE]
FROM Table

edited Jul 6, 2015 at 18:38

answered Jul 6, 2015 at 17:17

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mukesh Kalgude · Accepted Answer · 2015-07-06 17:19:58Z

2

try this query

SELECT ID,
CASE WHEN ISNUMERIC(COL_VALUE) = 1 THEN Convert(varchar(50), CONVERT(NUMERIC, COL_VALUE) * 1000)
  ELSE COL_VALUE 
END AS [COL_VALUE]
FROM Table

answered Jul 6, 2015 at 17:19

Mukesh Kalgude

4,8542 gold badges21 silver badges32 bronze badges

Collectives™ on Stack Overflow

Error converting data type varchar to numeric when using CASE

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related