Reducing text in a text field

I'm working on a view which selects a large text field from another table. (No, not the snapshots.) This table contains an errorlog from the web application that provides access to it. However, this errorlog contains some very long texts which cause the column to be extremely wide.

So I want to select the text field, but any word longer than 20 characters must be replaced to the first three, an ellipse (...) and the last three letters. Example:

 - System.Web.HttpException: The client
   disconnected. --->
   System.Web.UI.ViewStateException:
   Invalid viewstate. Client IP:
   xxx.xxx.xxx.xxx Port: xxxx
   User-Agent: Mozilla/4.0 (compatible;
   MSIE 7.0; Windows NT 6.1; WOW64;
   Trident/4.0; SLCC2; .NET CLR
   2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; InfoPath.3; .NET4.0C;
   .NET4.0E) ViewState:
   T89RiGYTOALETOKughad85KzoDRo/ut3Vlnd1qECsYf1t9GGNilDrvbRn8l45SVX8AsZrs6zyEngk8MKdpoRecI4j0X5IsmI0Deldf4nLKNlLoE4xaOoMUlj7hTFRXAvqyOFSvzsvyhwpWCLuG26RJT4YbGr1IJYgqWX9KVplIdasDUR0aNNMvD9FVa8Fi33Ny7fuIjxPKMpqKByKYJtAgZU4PIji88MMQWQDMNZmBxM965+bN+RsvTSxrGWzLLhzFCrK0LeCCrZeRQyncmMhUPGM9YB1+UapRfeca3R4vCacN6vv/UnZekBXP5rzAvDgJB9RGgXKhhdBVeCCD3h2N6X5RxKKLdy76h3AVZ8H8DfJpdjvre8ExNdkHeCOZWz0EjyX9hNMe2KZ2+gmEQaFi/mNJ5nZzCjOCfYsdbwAorpEGBZNqX9gBjDs32e8Admgdk+zvxl3Mt4pF3e6zTh45N1cCjBNCLya6Rd9d9mfiPM6DFbXUBiXw8dbZmT/6veCw0YPfRCjKCod3D7+Kva44RuiIkKIIqr0vPLLlja/ggH/4iHq9SqgOpwGJuoHGfy++5G14bIv3Zyzm8Vj+re3iMfbwZCUI4sCeFy7L7I6NrL27pd+6Qz60WAMv6lPl3Y88J5CUIVAIZqINsOt5htisTf00+d0pFptx5wwlmprzm1dVfXcL/0x3BPvlNDof5aW+PJxAP19SHR+PUuAEm9YO0ZmhGg+L5+DN2kuHSpQFpwC6FxCzVdicxlR8+x4jUy2+Fxeblabla

This mus开发者_开发技巧t become:

 - System.Web.HttpException: The client
   disconnected. --->
   System.Web.UI.ViewStateException:
   Invalid viewstate. Client IP:
   xxx.xxx.xxx.xxx Port: xxxx
   User-Agent: Mozilla/4.0 (compatible;
   MSIE 7.0; Windows NT 6.1; WOW64;
   Trident/4.0; SLCC2; .NET CLR
   2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; InfoPath.3; .NET4.0C;
   .NET4.0E) ViewState: T89...bla

And yes, that first text is extremely wide... Even SO has trouble displaying it correctly. :-)

So, I probably need to do something with regular expressions or whatever. Thus: SELECT REGEX(Something, something, something) FROM MyTable; or whatever.

Is it possible?

Maybe this will help

CREATE FUNCTION dbo.EllipseTextWords(@Text VARCHAR(MAX), @EllipsedSize INT, @Separator AS VARCHAR(1), @EllipsisLength INT = 3)
RETURNS VARCHAR(MAX)
AS
BEGIN

    DECLARE @Tokens TABLE
    (
        Token VARCHAR(MAX)
    )

    WHILE (CHARINDEX(@Separator, @Text) > 0)
    BEGIN
        INSERT INTO @Tokens
        SELECT LTRIM(RTRIM(SUBSTRING(@Text, 1, CHARINDEX(@Separator, @Text) - 1)))

        SET @Text = SUBSTRING(@Text, CHARINDEX(@Separator, @Text) + 1, LEN(@Text))
    END

    INSERT INTO @Tokens
    SELECT LTRIM(RTRIM(@Text))

    UPDATE @Tokens SET Token = LEFT(Token, @EllipsisLength) + '...' + RIGHT(Token, @EllipsisLength) WHERE Token <> '' AND LEN(Token) > LEN(LEFT(Token, @EllipsedSize))

    DECLARE @Ellipsed VARCHAR(MAX)
    SELECT @Ellipsed = COALESCE(@Ellipsed + @Separator, '') + Token FROM @Tokens 

    RETURN @Ellipsed

END

then you coul call the created function

SELECT dbo.EllipseTextWords(Details, 20, ' ', DEFAULT) FROM ErrorLog

but I wonder why dont you do it in the presentation layer.

How about the following solution. Assuming your table containing the errorlog is called ErrorLog and has a column Details which holds the large text you're referring to.

1. Create a special function that would split text content into words (took it from here but essentially any similar function will work):

   CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))     
   returns @temptable TABLE (items varchar(8000))     
   as     
   begin
       declare @idx int     
       declare @slice varchar(8000)
   
       select @idx = 1     
       if len(@String) < 1 or @String is null  return     
   
       while @idx != 0    
       begin     
          set @idx = charindex(@Delimiter, @String)     
          if @idx != 0     
            set @slice = left(@String, @idx - 1)     
          else     
            set @slice = @String     
   
          if(len(@slice) > 0)
            insert into @temptable(Items) values(@slice)     
   
          set @String = right(@String, len(@String) - @idx)     
          if len(@String) = 0 break     
        end 
        return     
   end
   

2. Then retrieve the text field from the ErrorLog table using the query below (basically, it iterates through all words, examines the length of each item and then concatenate them into a new string):

   SELECT 
   (
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(
           (SELECT (CASE WHEN LEN(items) > 50 
                    THEN LEFT(items, 3) + '...'  + RIGHT(items, 3) 
                    ELSE items
                    END) + ' '
            FROM dbo.Split(ErrorLog.Details, ' ') FOR XML PATH (''))
       ,'&','&')
       ,'&lt;','<')
       ,'&gt;','>')
       ,'&#x0A;',char(10))
       ,'&#x0D;',char(13))
       ,'&#x09',char(9))
    )
   AS Details 
   FROM ErrorLog
   

Please note that I use 50 as a threshold value because otherwise it will also cut items like 'System.Web.HttpException' which have length > 20.