CODEWITHSUNDEEP
mysqlsqltextreplacenull
willhelm2
willhelm2

Reputation: 43

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                    |

Upvotes: 0

Views: 3274

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

Use case expression for this:

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

Upvotes: 1

Dexter
Dexter

Reputation: 982

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

Upvotes: 2

Related Questions