Select a number of words from a string in mysql

[erikcw]

Hi all,

I have a series of long documents stored in mysql rows. I am trying to figure out how to select a section of of one of those documents to be displayed on the web.

Example.
Document 2000 words.
Query (or php) to select 500 words from the document. (sometime from word 1-to-500, but also sometimes from the middle of the string).

Does anyone have any ideas on how to break these large chunks of data into smaller chunks? (I would ideally like to use a SQL query to speed things up, but whatever gets the job done...)

Thanks!

[dmorison]

You could do it all in one query, but it would look a mess - something like:

SELECT (SUBSTRING(doc,FOOR(RAND((LENGTH(doc)-500))),500)) AS snippet FROM docs WHERE ...

In this example, docs is your table, doc is the field containing the text, and you would of course need a WHERE clause. It will return the snippet in a field called snippet.

I don't think doing this in MySQL rather than PHP is going to speed things up at all - the process of extracting snippet from doc has to be done sooner or later so it doesn't really matter who does it (the MySQL engine or the PHP engine).

[dmorison]

Oops - FOOR should read FLOOR in the above query...

[davelms]

A few similar questions in the past week or so...

[webmasterworld.com...]
[webmasterworld.com...]