Geek asks and answers

How to limit search and replace string in mySQL

I use this to find and replace strings in mySQL:

UPDATE products 
   SET prodname = REPLACE(prodname, " S", "'S")

Products contain lines such as "TYLENOL TABS 100 S" which I would like to convert to "TYLENOL TABS 100'S".

However, using the above SQL statement also affects strings such as "NIKE SHOES" that will become "NIKE'SHOES", which I clearly don't like.

Is there a way to limit the replacement function (or is there another way to do this) so that I can get the result I want? The "S" is usually at the end of the line for most of my data, and I hope this is useful.

+3
source share
3 answers

, , WHERE REGEXP():

UPDATE products SET prodname = REPLACE(prodname, " S", "'S") WHERE prodname REGEXP '[0-9]\sS'

, , , S. , MySQL REGEX, , , "TYLENOL TAB SOLO 100 S" "TYLENOL TAB'SOLO 100'S", "TYLENOL TABS 100 S"

+6

:

UPDATE products SET prodname = REPLACE(prodname, " S", "'S")
WHERE prodname LIKE "% S"

, prodname, S, . % char -.

+4

, prodname S, :

UPDATE products SET prodname = CONCAT(SUBSTR(prodname,1,CHAR_LENGTH(prodname)-2),"'S")
WHERE prodname LIKE "% S"

TYLENOL SOMETHING 100 S TYLENOL 'SOMETHING 100'S, TYLENOL SOMETHING 100'S. % char -, .

+1
source

More articles:


All Articles