How to delete variabilities from MySQL database?

Question

Is it possible to delete certain "text" from database which is not static?
For static text I simply replaced "static text" with "".
Actually I just need a command which will delete "static text1 * static text2"
So everything from text1 to text2.
Text1 and text2 is static, but everything between changes.

Thanks for help

Answer 1

If I understand correctly, you want to UPDATE a text-value in a table to remove any text that's between text1 and text2, but leave the actual row in-tact.

If that's the case, you can use a combination of SUBSTRING() and LOCATE() to perform the UPDATE:

UPDATE your_table SET
    field = 
        CONCAT(
            SUBSTRING(field, 1, LOCATE('text1', field) + 5),
            SUBSTRING(field, LOCATE('text2', field))
        )
WHERE
    field LIKE '%text1%text2%'

This will take the first part of the field column up-to the text "text1" (and including the text1 text itself), then will take a second sub-string from the field column for everything after (and including) the text2 text.

If you want to "change" the text in-between these two values, for instance say you have text1 some-value text2 and you want it to become text1 new-thing text2, you could use the above query and put the new text in the middle:

UPDATE your_table SET
    field = 
        CONCAT(
            SUBSTRING(field, 1, LOCATE('text1', field) + 5),
            ' new-thing ',
            SUBSTRING(field, LOCATE('text2', field))
        )
WHERE
    field LIKE '%text1%text2%'