Search code examples
mysqlsqltextreplacenull

SQL replace zero 0 values with NULL or text

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                    |
Solution

  • Use case expression for this:

    SELECT Productname ,
       CASE WHEN ProductPrice = 0 THEN NULL
            ELSE ProductPrice
       END as ProductPrice
  FROM table