Replace the Field Values in a Column Using Condition in SQL-CodePudding

here's a sample table I am working with:

ID      fruit 
first   apl
second  org
third   bnna

I am trying to extract apl and bnna from the fruit column and then replace those with "apple" and "banana"

I tried:

select ID, fruit as replace(fruit, "apl", "apple"), fruit as replace(fruit, "bnna", "banana")
from tbl
where fruit = 'apl' or
      fruit = 'bnna'

I am looking for:

ID      fruit 
first   apple
third   banana

CodePudding user response：

select id,
 case
  when fruit='apl' then 'apple'
  when fruit='bnna' then 'banana'
  else fruit 
 end as fruit
from your_table

in case you need to update the data

update your_table set fruit='apple' where fruit='appl';
update your_table set fruit='banana' where fruit='bnna';