jephperro wrote:
> 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 @[EMAIL PROTECTED]
varchar(100) ;
> SET @[EMAIL PROTECTED]
= 'BIOLOGICAL SURVEY FOUNDATION OF ONTARIO HAPPY IF SOMEONE
> CAN HELP SOLVE THIS SQL PROBLEM';
> SELECT
> @[EMAIL PROTECTED]
as ORIGINALSTRING,
> -- LEN(@[EMAIL PROTECTED]
) as [LengthOfOriginal],
> -- REVERSE(LEFT(@[EMAIL PROTECTED]
34)) as reverseL,
> 35-(charindex(' ', REVERSE(LEFT(@[EMAIL PROTECTED]
34)),0)) as
> LocationOfLastSpaceBeforeBreaking,
> SUBSTRING(@[EMAIL PROTECTED]
0, 35-(charindex(' ', REVERSE(LEFT(@[EMAIL PROTECTED]
34)),0)))
> as PART1,
> SUBSTRING(@[EMAIL PROTECTED]
35-(charindex(' ', REVERSE(LEFT(@[EMAIL PROTECTED]
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?
create table LongNames (
Name varchar(100)
)
insert into LongNames (Name) values (
'BIOLOGICAL SURVEY FOUNDATION OF ONTARIO HAPPY IF SOMEONE CAN HELP
SOLVE THIS SQL PROBLEM'
)
create table #SplitNames (
Name varchar(101),
Line1 varchar(35),
Line2 varchar(35),
Line3 varchar(35),
Line4 varchar(35)
)
insert into #SplitNames (Name)
select Name + ' ' from LongNames
update #SplitNames
set Line1 = substring(Name, 0, 35-(charindex(' ', reverse(left(Name,
34)),0))),
Name = substring(Name, 35-(charindex(' ', reverse(left(Name,
34)),0)), 100 )
update #SplitNames
set Line2 = substring(Name, 0, 35-(charindex(' ', reverse(left(Name,
34)),0))),
Name = substring(Name, 35-(charindex(' ', reverse(left(Name,
34)),0)), 100 )
update #SplitNames
set Line3 = substring(Name, 0, 35-(charindex(' ', reverse(left(Name,
34)),0))),
Name = substring(Name, 35-(charindex(' ', reverse(left(Name,
34)),0)), 100 )
update #SplitNames
set Line4 = substring(Name, 0, 35-(charindex(' ', reverse(left(Name,
34)),0))),
Name = substring(Name, 35-(charindex(' ', reverse(left(Name,
34)),0)), 100 )
select * from #SplitNames
drop table #SplitNames
drop table LongNames
|
