CODEWITHSUNDEEP
mysqlsqlstringreplace
Andrés Sanchez
Andrés Sanchez

Reputation: 19

Replacing wildcard in MySQL

I'm using this for replacing texts in a table in MySQL

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

But i don't know if it's possible to use wildcards for using this replacement function.

I have some texts like:

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

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

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

that i would like to change.

But i don't know how to make a rule for managing wildcards, as i would like to change the text whatever the number is it. Something like this:

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

Don't know if i can use something like this with the replace function, or do i have to do anyhting different in MySQL.

Thanks.

Upvotes: 1

Views: 802

Answers (2)

ikiK
ikiK

Reputation: 6532

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.

Schema (MySQL v5.7)

    INSERT INTO nametable (post_content) VALUES 
    ("alignnone size-full wp-image-4071"), 
    ("alignnone size-full wp-image-1762"), 
    ("alignnone size-full wp-image-8329"),  
    ("alignnone size-full wp-image-9837"), 
    ("something-else-9832"),
    ("alignnone NOT wp-image-9943"); 


    UPDATE nametable SET post_content = CONCAT("blank-",RIGHT(post_content,4));

---

**Query #1**

    SELECT * FROM nametable;

| id  | post_content |
| --- | ------------ |
| 1   | blank-4071   |
| 2   | blank-1762   |
| 3   | blank-8329   |
| 4   | blank-9837   |
| 5   | blank-9832   |
| 6   | blank-9943   |

---

View on DB Fiddle

Upvotes: 0

spencer7593
spencer7593

Reputation: 108380

There are no wildcards in the MySQL REPLACE function.

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

Reference:

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

Upvotes: 1

Related Questions