在MySQL中替换通配符

我正在用它替换MySQL中表中的文本

UPDATE `nametable` SET post_content = REPLACE(post_content,'XoldtextX','XnewtextX');

但是我不知道是否可以使用通配符来使用此替换功能。

我有一些文字,例如:

class="alignnone size-full wp-image-4071"

class="alignnone size-full wp-image-1762"

class="alignnone size-full wp-image-8329"

我想改变。

但是我不知道如何制定管理通配符的规则,因为无论数字是多少,我都想更改文本。像这样:

class="alignnone size-full wp-image-XXXX"

不知道我是否可以在replace函数中使用类似的功能,还是我必须在MySQL中做其他不同的事情。

谢谢。

评论
  • 简小兮
    简小兮 回复

    There are no wildcards in the MySQL REPLACE function.

    Introduced in MySQL 8.0 is REGEXP_REPLACE function. (Not available in earlier versions.)

    参考:

    https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace

  • 零零柒
    零零柒 回复

    You can do something like this if you want to change all texts. You can change numbers also with same technique, and specific text and numbers by adding where clause. You can change LEFT into RIGHT or use SUBSTRING. See CONCAT and RIGHT used in this example. What ever suits your data. Example below is made for data provided in question.

    模式(MySQL v5.7)

        UPDATE nametable SET post_content = CONCAT("newTEXT-",RIGHT(post_content,4));
    
    ---
    
    **Query #1**
    
        SELECT * FROM nametable;
    
    | id  | post_content |
    | --- | ------------ |
    | 1   | newTEXT-4071 |
    | 2   | newTEXT-1762 |
    | 3   | newTEXT-8329 |
    
    ---
    

    View on DB Fiddle