从MySQL提取前X个单词(不仅仅是字符) [英] Pull first X words (not just characters) from mySQL

问题描述

我希望能够从数据库字段中提取前X个单词以用于预览.基本上，如果字段的内容是

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."

我想回声

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

有更好的方法吗?

推荐答案

您肯定要使用

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.

注意:这将返回标在最后一个单词后面的标点符号，可能不希望使用.在PHP中替换字符串末尾的所有标点符号就足够简单了，但是如果您想完全保留在SQL中，我想您可以使用

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

可能有一个更好的选择来删除结尾的标点符号-但这也许是另一个问题(我仍在寻找一种比TRIM更好的解决方案).

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

这篇关于从MySQL提取前X个单词(不仅仅是字符)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！