Wednesday, November 23, 2016

Changing '& to '&' unless the '&' is used alone. (like « --> « but & is not changed to &)

I used the following SQL to change all "&" to "&" except when "&" is used just to express "&" alone. Because my website was showing "«" as "«", which I don't like.

UPDATE `your_table`
SET `your_field` = REPLACE(`your_field`, '&', '&')
WHERE `your_field` LIKE '%&%' AND `your_field` NOT LIKE ' & '

As a result:
« --> «
& --> &

But please note, this doesn't work if a word to be replaced and a word to not be replaced are used in a same field at a same time. For example:
I & my girlfriend went the zoo. See these pics » http://www.blabla...
This has both of "&" and "»" in a same sentence, which means it mixedly has a word to be replaced and a word to not be replaced at a same time...So I guess "&" would not replaced at all in this sentence.