asked on

SQL Server splits long text into multiple lines

Hi I have a very long text field column that is passed as a parameter. I want to take that text and split them into multiple lines. Let's say I want to split them in every 50th character. I tried different functions using LEFT and Substring but it cuts a word when that 50th character falls in. I want to be able to not cut the word but find the the next space and break that into a line.

How can I achieve that? Please assist
Thank you

HainKurt

why do you need such thing?
maybe there is a better approach to do what you need...
also, what is max length of that string? less than 100, or can be like 5000!
and after splitting it, what you will do?

angel7170

ASKER

I need to display them as a separate row in SSRS report. I have a special formatting to underline and double spacing. I tried different approach in SSRS and none of them seem to work as I expected.
The max length will be around 1000

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

the splitted lines are greater or equal to length parameter, not less than that :)
it is a bit more complicated than this function...
you may find this useful...
or you/somebody enhance it and make lines less than or equal to parameter...
I dont have time to do that for now...

HainKurt

if you want, fixed length, usage will be

declare @str as nvarchar(max) ='I need to display them as a separate row in SSRS report. I have a special formatting to underline and double spacing. I tried different approach in SSRS and none of them seem to work as I expected. The max length will be around 1000';

select dbo.splitstr(@str, 20, '', Char(10))

I need to display th
em as a separate row
 in SSRS report. I h
ave a special format
ting to underline an
d double spacing. I 
tried different appr
oach in SSRS and non
e of them seem to wo
rk as I expected. Th
e max length will be
 around 1000

Open in new window

angel7170

ASKER

I see the output and the words are getting cut. For example "them" is cut into "th" on the first line and "em" on the second line. I need the output to not cut the words.

HainKurt

so, use ID: 421965975
same function can be used for all purposes...

declare @str as nvarchar(max) ='I need to display them as a separate row in SSRS report. I have a special formatting to underline and double spacing. I tried different approach in SSRS and none of them seem to work as I expected. The max length will be around 1000';

select dbo.splitstr(@str, 20, ' ', Char(10))

I need to display them
as a separate row in
SSRS report. I have a
special formatting to
underline and double
spacing. I tried different
approach in SSRS and
none of them seem to
work as I expected. The
max length will be around
1000

Open in new window

you said " I want to split them in every 50th character" and thats why I showed how to use that way above...

angel7170

ASKER

Thank you. This works but I am unable to do double spacing in SSRS with what is returned on here. How can I get that double line when it breaks. Is there a easy expression I can write?

HainKurt

what is double line?

what do you need as perpetrator? maybe one of these

select dbo.splitstr(@str, 20, ' ', Char(10)+Char(10))

Open in new window

select dbo.splitstr(@str, 20, ' ', Char(10)+Char(13))

Open in new window

angel7170

ASKER

Worked perfect. Thank you