SQL Query

[Charisma Bypass]

If I had a bunch of records in which 1 row contained a certain words (let's say it's "plonker" for arguments sake), what SQL would I run to entirely remove any row which contains the word plonker?

Eg: 

ID: 1
Name: Burt
Content:  is a plonker

How would I make "Content" blank?  But for all the records in the DB that contained the word.  And if Content didn't contain "plonker" it would just stay as is, unchanged.

Thanks in advance for any help.

[Charisma Bypass]

Hmmmn.

Code:

DELETE FROM Table WHERE Content LIKE '%plonker%'

That should do it?  Or maybe

Code:

UPDATE Table SET Content = '' WHERE Content LIKE '%plonker%'

Would be better?

[sarahA]

this will empty the content field

Code:

UPDATE tablename SET content = '' WHERE content LIKE '%plonker%'

this will remove the entire record

Code:

DELETE FROM tablename WHERE content LIKE '%plonker%'

This will find any version of the word plonker however, be it a whole word or part of a word (eg. splonker). If you want just words then you'd need to do something like

Code:

content LIKE '% plonker %' OR content LIKE '% plonker,%' OR content LIKE '% plonker.%'

and so on, covering any type of symbol that could be after the word.

hth

[JasonD]

and so on, covering any type of symbol that could be after the word.

WHERE content REGEXP '(^|[^[:alpha:]])plonker($|[^[:alpha:]])'

[sarahA]

Or that too...

[Charisma Bypass]

Thanks guys, you've really helped me out

[Charisma Bypass]

Say I have a row (fulltext) which contains 10 paragraphs of text.  How would I strip out the first 2 or 3 paragraphs of text from it and put the stripped out stuff into a different row?

Basically, create an `excerpt` taken from some `content`...

Thanks in advance, you lovely people!

[sarahA]

I think (although you'd need to try this or wait for Jason or Jee to come along and give a better version..!) you could do

Code:

UPDATE tablename SET excerpt = LEFT(content, 200)

That would just copy it over. If you want to then remove that from the content as well then this may work

Code:

UPDATE tablename SET excerpt = LEFT(content, 200), content = RIGHT(content, LENGTH(content)-200)

However I'm not 100% certain the second bit would work in one query, you may need to do the first one and then the second.

[JasonD]

Code:

UPDATE table SET excerpt = CONCAT(SUBSTRING_INDEX(content, '</p>', 3), '</p>')

Where 3 is the number of paragraphs and '</p>' is how they are marked, you have to concat it back in as it will cut off before it. if it is plain text then you probably want '\r\n\r\n' instead of '</p>'.

If you want to remove the excerpt from the rest of the content you will need a second query:

Code:

UPDATE table SET content = SUBSTRING(content, LENGTH(excerpt) + 1)

[Charisma Bypass]

Thanks Guys.  Very much appreciated.