SQL Statement Problem : Separate long varchar into word seqments 35 chars long

[jephperro]

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

[Erland Sommarskog]

jephperro (jeff.pe...@gmail.com) writes:
> 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.

T-SQL is definitely a poor choice for this sort of job. If you are on
SQL 2005, write a function in C# or VB .Net for the task. Probably
you should use the RegEx classes.

If you are on SQL 2000, try to find solutions client-side.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[eis...@gmail.com]

You might also want to look into address normalization tools,
depending on how much data you're talking about. The standardized/
normalized USPS addresses are a lot shorter than the addresses people
tend to give you. Things like Avenue are shortened to AVE and North
becomes N, etc. Normalizing the addresses might help you out a lot.

If you put in an address here at the USPS site in the link below, it
will normalize it as an example:
http://zip4.usps.com/zip4/welcome.jsp

You might be able to create a webservice that uses this website to
normalize all your addresses, or buy some third-party tool that does
the same.

That's probably going in another direction from what you're thinking,
but if you're mailing items, it's also worth the cost to verify that
the addresses are valid before mailing.

[--CELKO--]

Look up mailing lists tools from Group 1 or Melissa Data. Life is too
short to write your own package.

[steve]

Here is a relatively simple iterative solution to this problem:

'Splitting a string into fixed sized word segments'
http://beyondsql.blogspot.com/2008/05/dataphor-splitting-string-into-fixed.html

A relational system greatly simplifies problems like this :-)

best,
steve
www.beyondsql.blogspot.com

[steve]

The solution to this problem at:

'Splitting a string into fixed sized word segments'
http://beyondsql.blogspot.com/2008/05/dataphor-splitting-string-into-fixed.html

has been updated. I've tried to make it easier to understand. The
comments, I hope, are clearer. If anyone has ideas for further
development, enhancements or direction, by all means contact me:) If
someone is in a pinch and this can help let me know. I'm always happy
to help someone out.

best,
www.beyondsql.blogspot.com

[Ed Murphy]

jephperro wrote:

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