SQL replace zero 0 values with NULL or text-CodePudding

I have a table Products
Some ProductPrice values are 0 (type is decimal(10,0))

ID   | Productname |      ProductPrice                    |
 ---- ------------- -------------------------------------- 
|  1 | ShirtXS     |      299                             |
|  2 | TrousersM   |        0                             |

ProductPrice is DECIMAL(10,0).
How to write SQL query to convert/replace ProductPrice value 0 with NULL or text N/A?
Desired output:

ID   | Productname |      ProductPrice                    |
 ---- ------------- -------------------------------------- 
|  1 | ShirtXS     |      299                             |
|  2 | TrousersM   |       NULL or N/A                    |

CodePudding user response：

use case when

select Productname ,
case when ProductPrice=0 then null 
else ProductPrice end as ProductPrice
from table

CodePudding user response：

I'm not sure if you are looking to UPDATE or SELECT. If you are looking for SELECT you can use NULLIF() from MySQL to replace 0 with NULL. The syntax is below:

SELECT *,NULLIF(<column_name>,0) as <variable_name> from <table_name>;