SQL Server Substring Breaking Words, Not Characters

Question

I would like to show no more than n characters of the text field in the search results to give the user an idea of ​​the content. However, I cannot find a way to easily break words, so I end up with a partial word during the break.

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

I would prefer the system to be displayed before the character n characters where the words are stored, so I would like to see:

"This student has not presented his last few"

Is there a closest word function that I could write in T-SQL, or do I need it when I return the results back to ASP or .NET?

Answer 1

I recommend doing this kind of logic outside the database. With C #, it might look something like this:

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

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

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

You can do this with T-SQL, but this is a bad idea (poor performance, etc.). If you really need to put it in the database, I would use a CLR-based stored procedure.

Answer 2

If you must do this 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))))

It will not be catastrophically slow, but it will definitely be slower than doing it at the presentation level.

— - . , , , .

Answer 3

, , , /. , .

:

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 4

, , . , , . :

• - .
• - , . Over-exposure, , , , . , 01-02-1985?
• - . , , [e-mail] $79,95?
• - 1,239 , .
• - O'Reily , SQL - "Enterprise" Database.
• ?: 1: TP - , ?

Answer 5

I found the answer to this site and changed it:

• cast (150) must be greater than the number of characters returned (100)

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

Answer 6

I'm not sure how fast this will work, 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 7

I would not advise doing this either, but if you need to, 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