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 [LengthOfOrigina l],
-- REVERSE(LEFT(@f ind, 34)) as reverseL,
35-(charindex(' ', REVERSE(LEFT(@f ind, 34)),0)) as
LocationOfLastS paceBeforeBreak ing,
SUBSTRING(@find , 0, 35-(charindex(' ', REVERSE(LEFT(@f ind, 34)),0)))
as PART1,
SUBSTRING(@find , 35-(charindex(' ', REVERSE(LEFT(@f ind, 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 6 4267
jephperro (je************ @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, es****@sommarsk og.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.
Look up mailing lists tools from Group 1 or Melissa Data. Life is too
short to write your own package.
Here is a relatively simple iterative solution to this problem:
'Splitting a string into fixed sized word segments' http://beyondsql.blogspot.com/2008/0...nto-fixed.html
A relational system greatly simplifies problems like this :-)
best,
steve www.beyondsql.blogspot.com
On May 9, 12:45 pm, jephperro <jeff.perrea... @gmail.comwrote :
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.
The solution to this problem at:
'Splitting a string into fixed sized word segments' http://beyondsql.blogspot.com/2008/0...nto-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
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 @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 [LengthOfOrigina l],
-- REVERSE(LEFT(@f ind, 34)) as reverseL,
35-(charindex(' ', REVERSE(LEFT(@f ind, 34)),0)) as
LocationOfLastS paceBeforeBreak ing,
SUBSTRING(@find , 0, 35-(charindex(' ', REVERSE(LEFT(@f ind, 34)),0)))
as PART1,
SUBSTRING(@find , 35-(charindex(' ', REVERSE(LEFT(@f ind, 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(Na me,
34)),0))),
Name = substring(Name, 35-(charindex(' ', reverse(left(Na me,
34)),0)), 100 )
update #SplitNames
set Line2 = substring(Name, 0, 35-(charindex(' ', reverse(left(Na me,
34)),0))),
Name = substring(Name, 35-(charindex(' ', reverse(left(Na me,
34)),0)), 100 )
update #SplitNames
set Line3 = substring(Name, 0, 35-(charindex(' ', reverse(left(Na me,
34)),0))),
Name = substring(Name, 35-(charindex(' ', reverse(left(Na me,
34)),0)), 100 )
update #SplitNames
set Line4 = substring(Name, 0, 35-(charindex(' ', reverse(left(Na me,
34)),0))),
Name = substring(Name, 35-(charindex(' ', reverse(left(Na me,
34)),0)), 100 )
select * from #SplitNames
drop table #SplitNames
drop table LongNames This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Uthuras |
last post by:
Greetings,
Machine : Pentium IV
Os Windows 2000 server
Product : DB2 UDB Release : 7.2
We are fail to load the following data file format into db2 database
table that has long varchar data type. The load command with
'delprioritychar' option does not seem to help much on the loading
process.
|
by: Alexandre H. Guerra |
last post by:
I needed to log all statements executed during a period of time
and now i need ordering the long varchar column in the statements monitor table
(STMT_TEXT)
Is there any flag to set to release the DB2 255 char wide ordering limit ?
Thanks
Alexandre
|
by: Volker |
last post by:
Hi,
we use UDB 7 FP 12 on a Solaris 8 machine. I try to load data from an
IXF file to a table containing one column with the data type LONG
VARCHAR FOR BIT DATA. If I do so, it seems that the load-utility loops
and does nothing.
top shows one process with maximum cpu load. There are no I/O waits,
but kernel load is very high. Traces on HDD/SAN show no or only very
less I/O activity. There are about 4,000,000 rows in the IXF file and
|
by: Scav |
last post by:
Helpful folks,
I recently applied fixpacks to our test server to bring it from V8.1.3
up to V8.2.2. Everything seemed to work fine except a DCOM component
that deals with Long Varchar column on one of our tables. I was
wondering if V8.2.2 no longer supports Long Varchar columns. Does
anyone know if this data type has been 'decommissioned'?
Any help or feedback would be greatly appreciated.
Thanks,
|
by: James o'konnor |
last post by:
hello.
i have the next for create one table into db2
CREATE TABLE "MYSQUEMA"."TABLADEMO" (
"ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +0
INCREMENT BY +1
MINVALUE +0
MAXVALUE +2147483647
NO CYCLE
| |
by: mark |
last post by:
I have come across a problem that has me baffled.
I am using PHP with MySQL 4.1.14. Whenever I execute an update
statement against a specific table, some of the varchar fields do not
get set to the new values, but get set to empty strings instead.
I have simplified the code down to updating a single column in the
table, but the same results happen when updating just the single
column.
|
by: John Smith |
last post by:
What is the difference? Is LONG VARCHAR only 28 bytes longer then VARCHAR?
Is this the only difference?
|
by: Okonita |
last post by:
Hi everyone,
I have been building a set of tables for a new database and having
problem with tables having columns defined as Long Varchar. Can
someone look this over and tell me what I am doing wrong with the
create statement shown below:
Error Msg:
DB21034E The command was processed as an SQL statement because it was
|
by: jyoti kelkar |
last post by:
Hi
I am using following formula to find
end point lat long given start point lat long azimuth and distance
Latitude(In degrees) + radius (in degrees) * Math.Sin(90 - Azimuth)(in radians)
It does not give me correct result when the point is other than USA
Thanks
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |