CODEWITHSUNDEEP
mysql
lilsizzo
lilsizzo

Reputation: 366

Replace 0 only in mysql

So, im just trying to replace ONLY '0' in the table where has a column propertyDetailBedroom that is a string. The below is the example of data from the table.

Table 
1 | 0
2 | 10
3 | 10+1



select replace(propertyDetailBedroom,"0","Studio") from table

From the above code, it shows that i would like the whole list to be returned but only to replace the value 0 to be Studio without changing 10 or 10+1. This query that i have would replace every 0 in the table. And that is not the expected result.

Table
1 | Studio
2 | 1Studio
3 | 1Studo+1

My expected result would still return all of the string but only to replace the only value that has 0.

Table
1 | Studio
2 | 10
3 | 10+1

Upvotes: 0

Views: 312

Answers (2)

Rajat
Rajat

Reputation: 5803

CASE is probably a better alternative, but here is another method using string operators

select 
trim(replace(concat(' ',propertyDetailBedroom,' '), ' 0 ', ' Studio ')) 
from your_table

Upvotes: 1

forpas
forpas

Reputation: 164089

You don't need replace():

select 
  case propertyDetailBedroom
    when '0' then 'Studio' 
    else propertyDetailBedroom
  end 
from tablename

Upvotes: 1

Related Questions