Thinking outside the box

Human readable string truncation

In same cases, truncation of a long string is necessary. In most cases you just need to truncate it at the spot and have every section be exactly the same length, like this:

DECLARE     @String VARCHAR(MAX),
            @Size TINYINT

SELECT      @String = 'Hello my name is Jeff. I need some help on a project because ',
            @String = @String + 'right now this is how the application i am working ',
            @String = @String + 'with displays data.',
            @Size = 32

SELECT      1 + Number AS Part, 
            SUBSTRING(@String, Number * @Size, @Size)
FROM        master..spt_values
WHERE       Type = 'P'
            AND Number <= DATALENGTH(@String) / @Size
ORDER BY    Number

The output looks like this

Part
---- --------------------------------
   1 Hello my name is Jeff. I need so
   2 me help on a project because rig
   3 ht now this is how the applicati
   4 on i am working with displays da
   5 ta.

As you can see, the text is evenly split into exact same number of substring with same length.
But, what if you need to split the long string into a human-readable substrings?
Where every substring length is not longer than a fixed number and splitted at space characters?

Well, easy. There is a solution for that too!
See this piece of code:

DECLARE     @String VARCHAR(MAX),
            @Size TINYINT

SELECT      @String = 'Hello my name is Jeff. I need some help on a project because ',
            @String = @String + 'right now this is how the application i am working ',
            @String = @String + 'with displays data.',
            @Size = 45

;WITH Peso (Part, StartPos, Size)
AS (
    SELECT  1,
            1,
            CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, 1, @Size) + ' ', @Size))) AS INT)

UNION ALL

    SELECT  Part + 1,
            StartPos + Size + 1,
            CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, StartPos + Size + 1, @Size) + ' ', @Size))) AS INT)
    FROM    Peso
    WHERE   StartPos + Size <= DATALENGTH(@String)
)

SELECT      Part,
            SUBSTRING(@String, StartPos, Size)
FROM        Peso
ORDER BY    Part

Now, the output looks like this instead

Part
---- --------------------------------
   1 Hello my name is Jeff. I need
   2 some help on a project because
   3 right now this is how the
   4 application i am working with
   5 displays data.

Quite a difference, huh?

Print | posted on Wednesday, March 18, 2009 11:11 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]