Jay-Jay Jay-Jay - 1 year ago 92
MySQL Question

MySQL Query - replace or remove 0 (zero) in a column, but not remove the digit zero in a word or number?

I have a table where one column has 0 for a value. The problem is that my page that fetches this data show's the 0.

I'd like to remove the 0 value, but only if it's a single 0. And not remove the 0 if it's in a word or a numeric number like 10, 1990, 2006, and etc.

I'd like to see if you guys can offer a SQL Query that would do that?

I was thinking of using the following query, but I think it will remove any 0 within a word or numeric data.

update phpbb_tree set member_born = replace(member_born, '0', '')

Hopefully you guys can suggest another method? Thanks in advance...

Answer Source

After discussed at the comments you have said that you want to not show 0 values when you fetching the data. The solution is simple and should be like this.

lets supposed that you have make your query and fetch the data with a $row variable.

if($row['born_year'] == '0'){
    $born_year = "";
} else {
    $born_year = $row['born_year'];

Another solution is by filtering the query from the begging

select * from table where born_year !='0';


if you want to remove all the 0 values from your tables you can do it in this way. Consider making a backup before.

update table set column='' where column='0'; 

if the value is int change column='0' to column=0

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download