Conversion of varchar to decimal

Pinku

SELECT CASE WHEN CAST(Replace([Discount],'%','') as decimal) = 0 THEN '0.00%' WHEN CAST(Replace([Discount],'%','') as decimal) IS NULL THEN '0.00%' ELSE CAST(Replace([Discount],'%','') as decimal(5,2)) END AS Discount FROM Table

This script is not working nd giving error as "Error converting data type varchar to numeric"

HOw to solve this issue

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naveen Kumar2y ago
Accepted answer
The error you're encountering usually happens when the CAST function tries to convert a value that isn't a valid number. To handle cases where the Replacefunction meNULL, you should use TRY_CAST instead of CAST. TRY_CAST will return NULL if the conversion fails, which can then be handled in your CASE statement.

Here’s a revised version of your query that uses TRY_CAST:

Make sure to adjust the precision and scale in decimal(5,2) if needed, depending on your actual data requirements.
```
SELECT CASE 
           WHEN TRY_CAST(REPLACE([Discount], '%', '') AS decimal(5,2)) IS NULL OR TRY_CAST(REPLACE([Discount], '%', '') AS decimal(5,2)) = 0 THEN '0.00%'
           ELSE FORMAT(TRY_CAST(REPLACE([Discount], '%', '') AS decimal(5,2)), '0.00%')
       END AS Discount
FROM Table;
```
Naveen Kumar2y ago
Pinku, the values you gave '000.22' and '00.000' were returning result without any issue with the same query.

What is the datatype of the Discount column?
Pinku2y ago
So Naven you are right its working for both 50 and 50 % . BUt now i found exact issue. If discount value is like '000.22' or '00.000'. so before decimal if no value is there then its throwing error.How can i solve this issue if you can help Please help
Naveen Kumar2y ago
Hi Pinku

For 50 and 50%, I'm getting the output.

What error you are getting? and what is the datatype of discount column in the table?
Pinku2y ago
Thanks Jayraj and Chetan.Now i found the exact issue.

select CASE WHEN CAST(Replace(@Discount,'%','') as decimal) = 0 THEN '0.00%' WHEN CAST(Replace(@Discount,'%','') as decimal) IS NULL THEN '0.00%' ELSE CAST(Replace(@Discount,'%','') as decimal(5,2)) END AS Discount

select CASE WHEN TRY_CAST(Replace(@Discount,'%','') as decimal) = 0 THEN '0.00%' WHEN TRY_CAST(Replace(@Discount,'%','') as decimal) IS NULL THEN '0.00%' ELSE TRY_CAST(Replace(@Discount,'%','') as decimal(5,2)) END AS Discount
Both the abovce query are working but on a certion condition its not woring and giving error as "Error converting data type varchar to numeric"
So the condition id if discount value is like '000.22' . so before decimal if no value is there then its throwing error.How can i solve this issue if you can help Please
Jayraj Chhaya2y ago
```
SELECT 
    CASE 
        WHEN ISNUMERIC(REPLACE([Discount],'%','')) = 1 
            THEN CAST(REPLACE([Discount],'%','') AS DECIMAL(5,2)) 
        ELSE 0.00 
    END AS Discount 
FROM Table
```
To resolve the "Error converting data type varchar to numeric" issue, ensure that the VARCHAR column [Discount] contains only numeric values before attempting the conversion to DECIMAL. You can modify the script to handle non-numeric characters like '%' before casting to DECIMAL.

Chetan Sanghani2y ago

Please try this.

SELECT 
    CASE 
        WHEN TRY_CAST(REPLACE([Discount], '%', '') AS decimal(5,2)) = 0 THEN '0.00%'
        WHEN TRY_CAST(REPLACE([Discount], '%', '') AS decimal(5,2)) IS NULL THEN '0.00%'
        ELSE CAST(TRY_CAST(REPLACE([Discount], '%', '') AS decimal(5,2)) AS varchar) + '%'
    END AS Discount 
FROM Table

Pinku2y ago
Hello Naveen
Thanks for your response.Its working if Discount is 50 but not working if Discount is 50%. When % added then this script is not working. Any suggestion on this.