使用SUBSTRING和CHARINDEX获取字段中的最后一个完整单词

Question

我的数据库中有一个字段，我计划用下面的语句将其截断为50个字符。

SELECT (CASE WHEN (LEN(Notes) > 50) THEN SUBSTRING(Notes, 0, 50) + '...' WHEN (LEN(Notes) < 50) THEN SUBSTRING(Notes, 0, LEN(Notes)) + '...' ELSE 'NO NOTES WERE ENTERED' END) AS Notes FROM MyTable

这工作得很好，但是，我想完成笔记字段中的最后一个单词，这样一个单词就不会被截断，所以我想使用CHARINDEX, SUBSTRING, REVERSE和可能的RIGHT函数来返回最后一个完整的单词，该单词是大约50个字符长的字符串的一部分。

我已经试验过了，但我运气不太好。

Answer 1

如果注释列的长度超过50，则可以使用CHARINDEX查找位置50后的下一个空格，并使用SUBSTRING查找该位置。

SELECT RTRIM(
    CASE
        -- when the length is less than 50, or the character at position 50 is part of the last word, return the entire string
        WHEN LEN(notes) < 50 OR (SUBSTRING(notes,50,1) <> ' ' AND CHARINDEX(' ',notes,50) = 0)
            THEN notes
        -- when the character at position 50 is a space, return the first 50 characters of the string
        WHEN SUBSTRING(notes,50,1) = ' '
            THEN LEFT(notes, 50)
        -- when the character at position 50 is a word, cut off the string at the next space after 50
        WHEN SUBSTRING(notes,50,1) <> ' '
            THEN LEFT(notes,CHARINDEX(' ',notes,50))
    END) AS first_50
FROM tbl_notes

Answer 2

如果你不想超过字符限制，你可以这样设置:

DECLARE @Table TABLE
(
  String nvarchar(100)
)
DECLARE
  @Size int             = 25
INSERT @Table SELECT 'Lorem ipsum dolor sit ame'
INSERT @Table SELECT 'Lorem ipsum dolor sit ame tas'
INSERT @Table SELECT 'Lorem ipsum dolor sit am asd'
INSERT @Table SELECT 'Lorem ipsum dolor sita am'
INSERT @Table SELECT 'Lorem ipsum dolor sita a amet, consectetur adipiscing elit,'
INSERT @Table SELECT 'Lorem ipsum dolor sita'
INSERT @Table SELECT 'Lorem ipsum dolor sita asamet, consectetur adipiscing elit,'
SELECT
  LEN(R.LimitTruncation) AS LimitTruncationLen,
  R.LimitTruncation,
  LEN(String) AS StringLen,
  R.String
FROM
(
  SELECT
    String,
    --This is the main part
    CASE
      WHEN LEN(String) <= @Size THEN String
      WHEN CHARINDEX(' ', String, @Size) = @Size OR CHARINDEX(' ', String, @Size) = @Size + 1 THEN RTRIM(LEFT(String, @Size))
      ELSE REVERSE(SUBSTRING(REVERSE(LEFT(String, @Size)), CHARINDEX(' ', REVERSE(LEFT(String, @Size))), @Size))
    END AS LimitTruncation
  FROM
    @Table
) R