Convert Numeric to String

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;

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

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

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

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;

Please use below query.

SELECT salesno, 
       CASE WHEN '0' THEN '' ELSE '0' END, 
       CASE WHEN '0' THEN '' ELSE '0' END, 
       saleamount 
FROM sales;

Thanks