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
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, '&', '&')
,@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