SQL Server substring breaking on words, not characters

Question

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"

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"

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?

Answer 1

I recommend doing that kind of logic outside database. With C# it could look similar to this:

static string Cut(string s, int length)
{
    if (s.Length <= length)
    {
        return s;
    }

    while (s[length] != ' ')
    {
        length--;
    }

    return s.Substring(0, length).Trim();
}

Of cause you could do this with T-SQL, but that is bad idea (bad performance etc.). If you really need to put it inside DB I would use CLR-based stored procedure instead.

Answer 2

I found an answer on this site and modified it:

• the cast (150) must be greater than the number of characters you're returning (100)

  LEFT (Cast(myTextField As varchar(150)), CHARINDEX(' ', CAST(flag_myTextField AS VARCHAR(150)), 100) ) AS myTextField_short

Answer 3

I'm not sure how fast this will run, but it will work....

DECLARE @Max  int

SET @Max=??

SELECT
   REVERSE(RIGHT(REVERSE(LEFT(YourColumnHere,@Max)),@Max- CHARINDEX(' ',REVERSE(LEFT(YourColumnHere,@Max)))))
    FROM YourTable
    WHERE X=Y

Answer 4

I agree with doing this outside of the database that way other applications with different length restrictions can make their own decisions on what to show/hide. Perhaps that can be a parameter to the database call though.

Here's a quick stab at a solution:

DECLARE @OriginalData NVARCHAR(MAX)
    ,@ReversedData NVARCHAR(MAX)
    ,@MaxLength INT 
    ,@DelimiterPosition INT ;

SELECT @OriginalData = 'This student has not submitted his last few assignments'
    ,@MaxLength = 45;

SET @ReversedData = REVERSE(
         LEFT(@OriginalData, @MaxLength)
        );

SET @DelimiterPosition = CHARINDEX(' ', @ReversedData);

PRINT LEFT(@OriginalData, @MaxLength - @DelimiterPosition);

/*
This student has not submitted his last few assignments
1234567890123456789012345678901234567890123456789012345
*/

Answer 5

I wouldn't advice to do that either, but if you must, you can do something like this:

DECLARE @text nvarchar(max);
DECLARE @end_char int;
SELECT @text  = 'This student has not submitted his last few assignments', @end_char = 50 ;

WHILE @end_char > 0 AND SUBSTRING( @text, @end_char+1, 1 ) <> ' '
 SET @end_char = @end_char - 1

SELECT @text = SUBSTRING( @text, 1, @end_char ) ;

SELECT @text

Answer 6

I'd like to add to the solutions already offered that word breaking logic is a lot more complicated than it seems on the surface. To do it well you are going to need to define a number of rules for what constitutes a word. Consider the following:

• Spaces - No brainer.
• Hyphens - Well that depends. In Over-exposed proably, in re-animated probably not. Then what about dates such as 01-02-1985?
• Periods - No brainer. Oh wait, what about the one in [email protected] or $79.95?
• Commas - In numbers such as 1,239 no, but in sentences yes.
• Apostrophes - In O'Reily no, in SQL is an 'Enterprise' Database tool yes.
• Do special characters alone constitute words?: In Item 1 : Buy TP is the colon counted as a word?