Hi there,

I'm having a really tough time with a SQL statement and I am wondering
if someone is able to help out or point me in the right direction.

I have a table of names which can be very long. These names get
printed on envelopes. The problem is the envelope can only hold 35
characters per line. I have to divide into 4 lines at most.

So I need to separate these long varchars into segments, no longer
than 35 characters but preserving whole words.

So far my approach has been to take a LEFT segment, REVERSE it, find
the first space with CHARINDEX and use it to calculate how many
characters to take in a SUBBSTRING.

Here's an example of what I have been trying. I can find the first
two segments, but then it starts to get confusing.


DECLARE @find varchar(100) ;
SET @find = 'BIOLOGICAL SURVEY FOUNDATION OF ONTARIO HAPPY IF SOMEONE
CAN HELP SOLVE THIS SQL PROBLEM';
SELECT
@find as ORIGINALSTRING,
-- LEN(@find ) as [LengthOfOriginal],
-- REVERSE(LEFT(@find, 34)) as reverseL,
35-(charindex(' ', REVERSE(LEFT(@find, 34)),0)) as
LocationOfLastSpaceBeforeBreaking,
SUBSTRING(@find, 0, 35-(charindex(' ', REVERSE(LEFT(@find, 34)),0)))
as PART1,
SUBSTRING(@find, 35-(charindex(' ', REVERSE(LEFT(@find, 34)),0)), 35 )
as PART2,
' ? ' as PART3,
' ? ' as PART4

Can anyone suggest a better approach? Am I going to be able to do
this in SQL?

I appreciate any help.

Jeff