如何限制搜索和替换MySQL中的字符串 [英] How to limit search and replace string in mySQL

问题描述

我正在使用它来搜索和替换mySQL中的字符串:

I'm using this to search and replace strings in mySQL:

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

产品包含诸如"TYLENOL TABS 100 S"之类的字符串，我想将其转换为"TYLENOL TABS 100'S".

The products contain strings such as "TYLENOL TABS 100 S" which I'd like to convert to "TYLENOL TABS 100'S".

但是，使用上面的SQL语句也会影响诸如"NIKE SHOES"之类的字符串，这些字符串将变成"NIKE'SHOES"，我显然不喜欢.

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

有没有办法限制替换功能(或者有其他方法可以做到这一点)，以便获得所需的结果?对于我的大多数数据，"S"通常位于字符串的尾部，我希望这会有所帮助.

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.

推荐答案

通过在WHERE子句中添加REGEXP()，可以将其限制为更符合您的需求的子集:

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'

这与产品名称中带有数字的行匹配，后跟一个空格，后跟一个S.可悲的是，在MySQL中，默认情况下没有实现REGEX替换，因此只能替换该匹配项，因此将"TYLENOL TAB SOLO 100 S"替换为"TYLENOL TAB'SOLO 100'S"，但不会替换您原来的"TYLENOL TABS 100 S"

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"

这篇关于如何限制搜索和替换MySQL中的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！