Use SUBSTRING AND CHARINDEX to get last complete w

问题:

I have a field in my database that I plan to truncate, for reporting purposes, to 50 characters with the statement below.

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

This works great, however, I would like to complete the last word in the notes field so that a word isn't cut off so I would like to use CHARINDEX, SUBSTRING, REVERSE and possibly the RIGHT function to return the last complete word that is part of a string roughly 50 characters long.

I have experimented and I'm not having much luck.

Any help would be appreciated.

回答1:

If the length of your notes column is over 50, you can use CHARINDEX to find the next space after position 50 and SUBSTRING to that position. Here is a SQL Fiddle.

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

回答2:

In case you don't want to exceed the character limit you could have it like this:

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