Convert Numeric to String

Question

Hi I have below select statement select salesno,'0' , '0',saleamount from sales i want 0 to display Blank Space. Thanks

Jayraj Chhaya · Answer

To display a blank space instead of the value 0 in a SQL select statement, you can use the CASE statement. Here's an example: SELECT salesno, CASE WHEN column1 = 0 THEN '' ELSE column1 END AS column1, CASE WHEN column2 = 0 THEN '' ELSE column2 END AS column2, saleamount FROM sales;

Naimish Makwana · Answer

The error you’re seeing is because SQL Server is trying to convert an empty string '' to a numeric data type, which is not possible. When you use UNION , SQL Server requires that the data types of the corresponding columns in the SELECT statements match. If you want to replace '0' with a blank space in a numeric column when using UNION , you’ll need to convert all the numeric columns to VARCHAR or NVARCHAR . Here’s an example: SELECT CAST(salesno AS NVARCHAR(50)), CASE WHEN column1 = '0' THEN '' ELSE CAST(column1 AS NVARCHAR(50)) END, CASE WHEN column2 = '0' THEN '' ELSE CAST(column2 AS NVARCHAR(50)) END, CAST(saleamount AS NVARCHAR(50)) FROM sales In this query, replace column1 and column2 with the actual column names where you want to replace '0' with a blank space. This query will check each row of column1 and column2 , and if the value is '0' , it will replace it with an empty string '' ; otherwise, it will keep the original value. Please note that this will convert your numeric columns to string columns, which might affect any subsequent numeric operations you want to perform on these columns. If you need to perform numeric operations on these columns after the UNION , you might need to convert them back to a numeric data type. Thanks

Ramco Ramco · Answer

Hi naimish Below is the hardcoed query . I have used union. When i write '' in place of 0 then it gives error "Error converting datatype varchar to numeric. Thanks

Muhammad Imran Ansari · Answer

Your question is not cleared. If you want to show empty/black space instead of '0' then you can use CASE statement like below otherwise improve your question. SELECT salesno, CASE WHEN '0' = 0 THEN '' ELSE '0' END AS column1, CASE WHEN '0' = 0 THEN '' ELSE '0' END AS column2, saleamount FROM sales;

Naimish Makwana · Answer

Please use below query. SELECT salesno, CASE WHEN '0' THEN '' ELSE '0' END, CASE WHEN '0' THEN '' ELSE '0' END, saleamount FROM sales; Thanks

Amit Mohanty · Answer

Hey Ramco, can you provide your full query along with union?

Convert Numeric to String

Answers (6)