SEO

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

jephperro (jeff.perreault@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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.