I have a MySQL DB with a column that sometimes has a value of "-1", how can I change that to "0" during the SELECT without doing a foreach loop on the result. Basically I want the 'display' value to be 0 if the DB value is -1. If the DB value is anything else then it should be displayed without modification.
Any ideas???
You can use case syntax:
select case
when col = -1 then 0
else col
end case
from Tab
or
select case col
when -1 then 0
else col
end case
from Tab
Here are more informations.
or if construct
select if(col=-1,0,col)
from Tab
Here are more informations.
A friend on App.net chimed in with:
SELECT IF(mycol=-1, 0, mycol)
Which seems to work a treat. :)
Or "IF" scalar function for shorter syntax
SELECT IF (col = -1, 0, col)
FROM Tab
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if
Another option then case statement is IF function as below given
//IF(expr,if_true_expr,if_false_expr)
SELECT IF(column = -1, 0, column) AS state FROM table
