Conversion of varchar to decimal

Question

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

Naveen Kumar · 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(52) if needed depending on your actual data requirements. SELECT CASE WHEN TRY_CAST(REPLACE([Discount] '%' '') AS decimal(52)) IS NULL OR TRY_CAST(REPLACE([Discount] '%' '') AS decimal(52)) = 0 THEN '0.00%' ELSE FORMAT(TRY_CAST(REPLACE([Discount] '%' '') AS decimal(52)) '0.00%') END AS Discount FROM Table;

Chetan Sanghani · Answer

Please try this. SELECT CASE WHEN TRY_CAST(REPLACE([Discount] '%' '') AS decimal(52)) = 0 THEN '0.00%' WHEN TRY_CAST(REPLACE([Discount] '%' '') AS decimal(52)) IS NULL THEN '0.00%' ELSE CAST(TRY_CAST(REPLACE([Discount] '%' '') AS decimal(52)) AS varchar) + '%' END AS Discount FROM Table

Naveen Kumar · Answer

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?

Naveen Kumar · Answer

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?

Jayraj Chhaya · Answer

SELECT CASE WHEN ISNUMERIC(REPLACE([Discount]'%''')) = 1 THEN CAST(REPLACE([Discount]'%''') AS DECIMAL(52)) 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.

Pinku · Answer

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

Pinku · Answer

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(52)) 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(52)) 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

Pinku · Answer

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.