0
votes

For example: In my table I have text field with following text:

The quick brown fox jumped over the lazy dog.

How can I find brown fox and delete it, so that new field value is:

The quick jumped over the lazy dog.

Is this possible to do with MySQL?

3

3 Answers

5
votes
update `table`
    set `field` = replace(`field`, 'brown fox ', '')
    where `field` = 'The quick brown fox jumped over the lazy dog.';

EDIT: As @Cranio pointed out, we need to remove spacing on either side of 'brown fox' in order to get the anticipated result.

0
votes
SELECT
CONCAT(
    LEFT("the quick brown fox jumps over the lazy dog",
        INSTR("the quick brown fox jumps over the lazy dog","brown fox")-1),
    MID("the quick brown fox jumps over the lazy dog",
        INSTR("the quick brown fox jumps over the lazy dog","brown fox")+
        LENGTH("brown fox"))
)

Using fields it would be

SELECT
CONCAT(
    LEFT(myField,
        INSTR(myField,substring)-1),
    MID(myField,
        INSTR(myField)+
        LENGTH(substring))
)
-1
votes

You can retrieve the field using like edit it, then save it back.