Navigation

	Home Page
	Index
	All Pages

Quick Search

Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Discuss (0) History

Word Wrapping - SQL Server

RSS

Modified on Tue, Aug 10, 2010, 4:14 PM by Administrator Categorized as SQL Server

Overview¶

This article presents two methods for wrapping text. The first is a simpler strategy: simply break the text into equal-sized chunks, breaking in the middle of a word if you have to. The second is a more complex strategy, but won't put a "line break" in the middle of a word.

Fixed Line Length¶

The following code demonstrates how to implement word-wrapping in T-SQL. Given a table (dbo.LoremIpsum) with a column (SampleText) containing text to be wrapped, and the maximum number number of characters per line (@MaxCharsPerLine), this SQL will return the first two "lines" of text, split at a space character, as SubLine and SubLine2.

{copytext|div1}

declare 
     @MaxCharsPerLine   int
    ,@StartPos          int

select
     @MaxCharsPerLine = 73
/*-----------------------------------------------------------------------------------------------*/
;
with a as (
    select 
        *
        ,StartPos   = case 
                        when len(SampleText) <= @MaxCharsPerLine then len(SampleText) 
                        else len(SampleText) - @MaxCharsPerLine  
                        end
        ,Backwards  = reverse(sampletext)
    from
        dbo.LoremIpsum
    where 1=1
        and id between 3 and 9
    )
/*-----------------------------------------------------------------------------------------------*/
,b as (
    select
         ID
        ,SampleText
        ,Pos        = case 
                        when charindex(' ', Backwards, StartPos) > 0 
                            then len(SampleText) - charindex(' ', Backwards, StartPos) 
                        else @MaxCharsPerLine 
                        end
    from
        a
    )
/*-----------------------------------------------------------------------------------------------*/
select
     *
    ,SubLine    = substring(SampleText, 1, Pos)
    ,SubLine2   = substring(SampleText, Pos+1, 1000)
from
    b

Wrapping at Spaces¶

This code requires SQL Server 2005

The following code is a function that, given a specified piece of text, will return a number of rows where each row is no longer that the maximum number specified, and each "line break" is at a space character.

{copytext|function}

if object_id(N'dbo.WrapText', N'TF') is not null
    drop function dbo.WrapText
go
create function dbo.WrapText
    (
     @InputText         varchar(1000)
    ,@MaxCharsPerLine   int
    ) returns @t table (SubLineNum int, SubLineDesc varchar(1000))
as begin

/*--- Inits ---*/
declare 
     @doc       xml
    ,@s         varchar(1000)
    ,@Delimiter varchar(2)

declare
     @WordList table (ID int identity(1,1), Word varchar(100))

select
     @Delimiter     = ' ' 
    ,@s             = replace(@InputText, '&', '&amp;')
    ,@s             = '<x><v>' + replace(@s, @Delimiter, '</v></x><x><v>') + '</v></x>'
    ,@doc           = convert(xml, @s)

/*--- Load WordList table ---*/
insert into 
    @WordList (Word)
select
     v  = T.c.value('v[1]', 'varchar(100)')
from
    @doc.nodes('x') T(c)

/*--- Build phrases ---*/
;
with MyData as (
    /* base case */
    select
         ID
        ,Word
        ,NewLine    = 0
        ,Phrase     = convert(varchar(1000), Word)
        ,MaxId      = (select max(id) from @WordList)
    from 
        @WordList
    where 
        ID = 1

    /* recursive case */
    union all select
         ID         = this.ID
        ,Word       = this.word
        ,NewLine    = case when len(prev.Phrase) + len(this.Word) + 1 > @MaxCharsPerLine then 1 else 0 end
        ,Phrase     = convert(varchar(1000), case 
                        when len(prev.Phrase) + len(this.Word) + 1 > @MaxCharsPerLine then this.Word 
                        else prev.Phrase + ' ' + this.word 
                        end)
        ,MaxId      = prev.MaxId
    from
        @WordList this
        inner join MyData prev
            on this.id = prev.id + 1
)
/*--- Output Results ---*/
insert into @T
select 
     SubLineNum     = row_number() over (order by this.ID)
    ,SubLineDesc    = this.Phrase
from 
    MyData this
    left join MyData next
        on this.id = next.id - 1
where 
    this.ID = this.MaxId
    or next.NewLine = 1

return
end