Take the 2-minute tour ×

How to change when the value is 0 then '-' in my sql especially the field have value percentage. Anyone know about this?

share|improve this question

6 Answers 6

up vote 2 down vote accepted

you can use IF

select IF(Myfield = 0 , '-', Myfeild) from table
share|improve this answer

Try the following Query:

CASE (column name) WHEN '0' THEN '-' ELSE (column name) END
from (table name)
share|improve this answer
Good answer, but you might have to add ELSE (column name) before the END. –  Jan Dvorak Dec 19 '12 at 7:23

try this query ::

SELECT replace('980','0','-') from dual;




REPLACE function is used to replace the desired characters from a string

share|improve this answer
MySQL does not have dual - this is not Oracle –  mvp Dec 19 '12 at 7:12
@mvp mySQL does have from dual. It's just neither required (in most cases) nor a table. –  Jan Dvorak Dec 19 '12 at 7:13

try this, this will find and replace the value

update [table_name] 
set [field_name] = replace([field_name],'0','-');
share|improve this answer

This statement will replace 0 to -, and display number as text with percent otherwise:

   CASE mycol WHEN 0
   THEN '-'
   ELSE concat(mycol, '%')
   END AS pct
FROM mytable

Arguably, this is better done on client side, but I leave it to another answer.

share|improve this answer

Try this <?php $query = "Select * from 0 in"; $result = str_replace('0', '-', $query); echo $result; ?>

share|improve this answer
u can use preg_match to check for the expression and then replace it –  Arunu Dec 19 '12 at 7:22

Your Answer


