CODEWITHSUNDEEP
sql-serversql-server-2012
user1804925
user1804925

Reputation: 159

Error converting data type varchar to numeric when using CASE

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.

Upvotes: 1

Views: 11377

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

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

Upvotes: 4

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

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

Upvotes: 2

Related Questions