Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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 ]

Feedback

Gravatar

# re: Human readable string truncation

Why do you want to do this in the database at all? Display is always done in the front end of a tiered architecture.
3/24/2009 7:18 PM | Joe Celko
Gravatar

# re: Human readable string truncation

Not when you need to export the data in smaller chunks because the recipient's file layout says messages can't exceed 32 bytes, but allows for many messages.

In the case above, Part is the message sequence number, and the 32 characters are the message.
3/24/2009 7:59 PM | Peso
Gravatar

# re: Human readable string truncation

I agree... this should be done in the front end code... if there is one. Heh... but there's not always a front end to be had so you better make sure you know how to do this in the back end.
12/8/2009 7:49 AM | Jeff Moden
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET