SQL Server子字符串破坏单词，而不是字符 [英] SQL Server substring breaking on words, not characters

问题描述

我想在搜索结果中显示的文本字段不超过n个字符，以使用户对内容有所了解。但是，我找不到轻松中断单词的方法，因此在中断时我只剩下了一部分单词。

I'd like to show no more than n characters of a text field in search results to give the user an idea of the content. However, I can't find a way to easily break on words, so I wind up with a partial word at the break.

当我想显示：学生尚未提交他的最后几次作业，系统可能会显示：该学生尚未提交他的最后几次作业

When I want to show: "This student has not submitted his last few assignments", the system might show: "This student has not submitted his last few assig"

我希望系统显示到保留单词的n个字符的限制，所以我想看看：

I'd prefer that the system show up to the n character limit where words are preserved, so I'd like to see:

这个学生还没有提交最后的几句话

"This student has not submitted his last few"

是否有我可以在T-SQL中编写的最接近的单词函数，或者当我将结果返回到ASP或.NET时应该这样做吗？

Is there a nearest word function that I could write in T-SQL, or should I do that when I get the results back into ASP or .NET?

推荐答案

如果必须在T-SQL中执行：

If you must do it in T-SQL:

DECLARE @t VARCHAR(100)
SET @t = 'This student has not submitted his last few assignments'

SELECT LEFT(LEFT(@t, 50), LEN(LEFT(@t, 50)) - CHARINDEX(' ', REVERSE(LEFT(@t, 50))))

这不会灾难性地慢，但是它将定义

除此之外，—仅切断单词并在较长的字符串后加上省略号也是不错的选择。这样，至少所有截断的字符串都具有相同的长度，如果要格式化为固定宽度的输出，这可能会派上用场。

Other than that — just cutting off the word and appending an ellipsis for longer strings is no bad option either. This way at least all truncated strings have the same length, which might come in handy if you are formatting for a fixed-width output.

这篇关于SQL Server子字符串破坏单词，而不是字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！