CODEWITHSUNDEEP
phpmysql
Sasha
Sasha

Reputation: 8705

PHP, MySQL - find a specific part in the field and delete it

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?

Upvotes: 0

Views: 158

Answers (3)

user1416258
user1416258

Reputation: 

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.

Upvotes: 5

Cranio
Cranio

Reputation: 9847

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))
)

Upvotes: 0

dado_eyad
dado_eyad

Reputation: 601

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

Upvotes: -1

Related Questions