Pull first X words (not just characters) from mySQL

Question

I'd like to be able to pull the first X words from a database field for use in a preview. Basically if a field 's content was

 "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Mauris malesuada."

I would like to echo

 "Lorem ipsum dolor sit amet... see more"

What's the best way to do this?

The only thing I know to do is pull the whole field in a query then do something like

$foo = [query_results];
$bar = explode(' ', $foo);
for($x=0, $x<6, $x++){
     echo $bar[$x];
};
echo "... see more"

Is there a better approach to this?

Answer 1

You definitely want to use SUBSTRING_INDEX which will return some number of characters until a specified count is achieved based on the occurrence of a delimiter. In your case the call would look like this:

 SELECT SUBSTRING_INDEX(text_field, ' ', 6) FROM ...

In particular, this will return up to six words where we define a word as a set of characters that are not spaces delimited by spaces.

Note: this will return punctuation attached to the last word, which may or may not be desired. It'd be simple enough to replace any punctuation characters at the tail of the string in PHP, but if you want to stay completely within SQL I think you can use TRIM. The syntax for that would be something like:

SELECT TRIM(TRAILING ',' FROM SUBSTRING_INDEX(text_field, ' ', 6)) FROM ...

There may be a better option for removing the trailing punctuation -- but perhaps that's another question (I'm still looking for a better solution than TRIM).