Q:

How to limit search and replace string in mySQL

I'm using this to search and replace strings in mySQL:
UPDATE products 
   SET prodname = REPLACE(prodname, " S", "'S")

The products contain strings such as "TYLENOL TABS 100 S" which I'd like to convert to "TYLENOL TABS 100'S".
However, using the above SQL statement also affects strings such as "NIKE SHOES", which will become "NIKE'SHOES" -- which I obviously don't like.
Is there a way I can limit the replace function (or is there another way to do this) so I can get the result I want? The " S" is usually at the tail end of the string for most of my data, which I'm hoping will be helpful.

A:

You could limit that to a subset more closed to your needs by putting a WHERE clause with a REGEXP():
UPDATE products SET prodname = REPLACE(prodname, " S", "'S") WHERE prodname REGEXP '[0-9]\sS'

This matches the rows that product names that have a number, followed by a space, followed by an S. Sadly there is no REGEX replace implemented by default in MySQL, to be able to replace only that match, so this will also replace "TYLENOL TAB SOLO 100 S" to "TYLENOL TAB'SOLO 100'S", but it won't replace in your original "TYLENOL TABS 100 S"

A:

You could try:
UPDATE products SET prodname = REPLACE(prodname, " S", "'S")
WHERE prodname LIKE "% S"

This means products with a prodname ending with S will be updated. The % char means anything.

