MYSQL - How to search and delete?

[Frostie]

Tweet

I'm wanting to run an SQL command from phpmyadmin that will search a FIELD in a TABLE and look for "xxxxx". Once it has found it, I want it to delete xxxxx and everything after it.

I can find all the posts by running a search such as:

SELECT * FROM `TABLE` WHERE `FIELD` LIKE '%xxxxx%'

I know how to do a simple search and replace for EXACT matches such as:

UPDATE TABLE SET FIELD = replace(FIELD,"xxxxx","yyyyy");

Using the above, I could find xxxxx and replace it with "" - however I want to delete xxxxx and everything after it, in the field. The content that follows xxxxx constantly changes so it has to be open ended.

Can anyone offer any advice?

[philhancox]

If I understand you correctly, I would do

UPDATE TABLE SET FIELD='' WHERE FIELD LIKE 'XXXX%'

[Frostie]

Surely it's not that easy!

I've just tried it and sadly it didn't work. It comes back with the MYSQL returned an empty result set (i.e. zero rows) error. I think this is due to the % only being on one side. If I change it %xxxxx% then it does obtain results, however I don't want any text BEFORE xxxxx to be deleted, only after.

[mrbishi]

surely that would set the whole field to blank, whereas I was under the impression you wanted to keep what was in front of the 'xxxxx' string?

[philhancox]

surely that would set the whole field to blank, whereas I was under the impression you wanted to keep what was in front of the 'xxxxx' string?

Yes it will; if the XXXX string is not at the beginning then we have a different story and I'll write something new. Frostie?

[Frostie]

Yup thats what I am trying to do.... so

RECORD 1
has this text and that text xxxxx then mumbo jumbo

RECORD 2
may have something like this and that before we see xxxxx that should be deleted

So the stuff in bold gets deleted (i.e. anything after xxxxx). Don't you just love my childish way of explaining stuff :blush

[mrbishi]

I've been having a look at the replace function and it seems that you can replace substrings, but im not sure if you can use that with the like function yet, am investigating. - can do it using php but not pure sql just yet!

[philhancox]

Ditto,
I'd do something like fetching the array, do a str_replace on the fields then update the database with the results. It would have to be a PHP script though

[KamikzMX]

I'm not too sure on MySQL Syntax, but converting what I would do in SqlServer to MySQL via the internet I would say you want to do:

UPDATE TABLE SET FIELD = LEFT(FIELD, LOCATE('xxxxx',FIELD)) WHERE FIELD LIKE '%XXXXX%'

[mrbishi]

Ditto,
I'd do something like fetching the array, do a str_replace on the fields then update the database with the results. It would have to be a PHP script though

yeah i'd explode on the 'xxxxx' string and replace with the the first element of the array. But as you said would need to be a php script as sql's string manipulation strings are quite basic.

[mrbishi]

I'm not too sure on MySQL Syntax, but converting what I would do in SqlServer to MySQL via the internet I would say you want to do:

UPDATE TABLE SET FIELD = LEFT(FIELD, LOCATE('xxxxx',FIELD)) WHERE FIELD LIKE '%XXXXX%'

awesome. top solution, i'd been looking at the left function but couldn't work out how to set the position parameter

[Frostie]

Thanks all :tup

Got it working using KamikzMX code. Happy days