使用SUBSTRING和CHARINDEX让现场最后一个完整的字(Use SUBSTRING AND

我在我的数据库中的字段，我计划截断，报告的目的，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，扭转和可能正确的函数返回的最后一个完整的词大致是50个字符长的字符串的一部分。

我已经尝试和我没有多少运气。

任何帮助，将不胜感激。

Answer 1:

如果您的笔记列的长度超过50，可以使用CHARINDEX找到位置50和后下空间SUBSTRING到该位置。 下面是一个SQL小提琴 。

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

文章来源: Use SUBSTRING AND CHARINDEX to get last complete word in field