MYSQL - REPLACE() function to entire string instead of part of it

Question

In column 1 I have 4 values

N
No
Y
Yes

I tried to replace N with No and Y with Yes with the following code

UPDATE nashupdated
SET SoldAsVacant = REPLACE(SoldAsVacant, "N", "No"),
    SoldAsVacant = REPLACE(SoldAsVacant, "Y", "Yes");

However, it also replaces the "Y" in "Yes" for example, becoming "Yeses"

How should I write the function so that it looks for the entire string only?

Try this method: https://stackoverflow.com/questions/50974562/sql-server-update-to-match-and-replace-only-exact-words — yildizyasin, Sep 15 '21 at 14:16

RiggsFolly · Answer 1 · 2021-09-15T14:49:03.827

1

Using an IF you could do it like this

UPDATE nashupdated
SET SoldAsVacant = IF(SoldAsVacant = 'N', 'No', SoldAsVacant ),
    SoldAsVacant = IF(SoldAsVacant = 'Y', 'Yes', SoldAsVacant );

This tests the existing value of the column and if it matches sets it to the new value and if not sets it to the existing value.

edited Sep 15 '21 at 14:49

answered Sep 15 '21 at 14:18

RiggsFolly

93,638
21
103
149

score 1 · Answer 2 · answered Sep 15 '21 at 15:02

1

UPDATE nashupdated
SET SoldAsVacant = REPLACE(SoldAsVacant, "N", "No"),
    SoldAsVacant = REPLACE(SoldAsVacant, "Y", "Yes")
WHERE SoldAsVacant IN ('Y', 'N');

This is more useful than IF/CASE, especially if SoldAsVacant is indexed.

answered Sep 15 '21 at 15:02

Akina

39,301
5
14
25

score 0 · Answer 3 · edited Sep 15 '21 at 14:19

0

Easy Way

UPDATE nashupdated set SoldAsVacant ='Yes' Where SoldAsVacant ='Y';
 
UPDATE nashupdated set SoldAsVacant ='No'  Where SoldAsVacant ='N';

edited Sep 15 '21 at 14:19

RiggsFolly

93,638
21
103
149

answered Sep 15 '21 at 14:17

Ayhan Sarıtaş

89
3

Check this for how to properly format code https://stackoverflow.com/help/formatting – RiggsFolly Sep 15 '21 at 14:20

score 0 · Answer 4 · answered Sep 15 '21 at 15:11

Use a CASE expression:

UPDATE nashupdated
SET SoldAsVacant = CASE SoldAsVacant 
                     WHEN 'N' THEN 'No'
                     WHEN 'Y' THEN 'Yes'
                   END
WHERE SoldAsVacant IN ('Y', 'N');

Or:

UPDATE nashupdated
SET SoldAsVacant = CASE WHEN SoldAsVacant = 'N' THEN 'No' ELSE 'Yes' END
WHERE SoldAsVacant IN ('Y', 'N');

score 0 · Answer 5 · answered Sep 15 '21 at 15:41

0

A fun way uses a derived table to lookup the values:

update nashupdated n join
       (select 'N' as old_value, 'No' as new_value union all
        select 'Y' as old_value, 'Yes' as new_value
       ) x
       on n.SoldAsVacant = x.old_value
    set n.SoldAsVacant = x.new_value;

What is nice about this approach is that the join filters down to only the rows that need to be updated and the values are mentioned only once.

answered Sep 15 '21 at 15:41

Gordon Linoff

1,242,037
58
646
786

There is no *fun* and nothing is *nice* when you use UNION ALL and then a join for such a simple requirement which can be done in just 1 scan of the table. This is terrible code. – forpas Sep 15 '21 at 15:46

MYSQL - REPLACE() function to entire string instead of part of it

5 Answers5