March 17, 2009 at 2:43 pm
I have an application im working that does not support wordwrap. I have a string value sometimes 750 chars long that is used as a comments field. I need to Break it down into 150 length segments so that i can enter each in its own field, but still make sure when the string is broken up i do not cut up words.
ex.
Wrong- how i am currently breaking the strings based on a fixed length.
Hello my name is Jeff. I need some help on a pro
ject becasue right now this is how the applicatio
n i am working with displays data.
Right- need to take in consideration spaces and whole words.
Hello my name is Jeff. I need some help on a
project because right now this is how the
application i am working with displays data.
March 17, 2009 at 3:04 pm
How about something like:
declare @String varchar(max);
select @String = 'Hello my name is Jeff. I need some help on a project because right now this is how the application i am working with displays data.';
select
case
when charindex(' ', reverse(substring(@String, number-50, 50)), 0) > 0 then substring(@String, number - charindex(' ', reverse(substring(@String, number-50, 50)), 0)+1, charindex(' ', reverse(substring(@String, number-50, 50)), 0)-1)
else ''
end +
substring(@String, number, 50 - charindex(' ', reverse(substring(@String, number, 50)), 0))
from dbo.Numbers
where number <= len(@String)
and number%50 = 0;
Note, this assumes you have a Numbers (or Tally) table. If not, you'll need to create one for it.
You'll also need to modify the 50s to whatever string length you're actually looking for. Maybe plug in a variable and make it an input parameter? Or use a fixed number. Whichever works best for what you're doing.
Keep in mind that this solution will create lines that are slightly longer than the stated length. It creates a substring, then goes back from it far enough to find a space and tacks that on. So line-length might be a bit longer than desired in some cases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2009 at 3:10 pm
Looks good, but what is the number table acctually doing?
If you could expand a bit on the whole numbers table that would be great.
Im hoping that with this i will be able to come out with,
5- 150 length string values FROM 1 - 750 length string
March 17, 2009 at 3:23 pm
[font="Verdana"]Following this with interest. GSquared, did you test your answer? I tried it, but it dropped off the first line for me.
BTW, I am wondering why you need to do this in SQL Server. Wouldn't it be better to reformat the string in a reporting tool or front end application?
[/font]
March 17, 2009 at 3:26 pm
An excellent article on the numbers or "tally" table can be found here [/url]. It's just a list of numbers from 1 to whatever.
In G's example, think of it as a join to various positions in your long string as if they were each a row in a table. This parses the long string into segments of about 50 bytes and then makes sure of the spaces at the end.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 3:57 pm
I have been handed a project where the reporting tool i am using does not have
this functionality built in to handle it. The developer before me was using sql server
to create the .XML that holds this data , and that is essentially sent to the application to
do the printing.
Trust me if i could do it any other way i would, but have been told to work with what i have.
March 17, 2009 at 4:01 pm
No problem. That question gets asked a lot when it seems as if the functionality should be implemented outside SQL server. What's the name of the reporting tool, so I can avoid it in the future?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 4:05 pm
It is built into a 3rd party software package i work with.
Essentially it is used for custom shipping labels, etc and while some of the features are great for creating
dynamic packing lists with shipping labels merged in at the time of shipment, which cannot be done
nicely in crystal, it has its limitations as well.
So pretty much its the best of both worlds.
I used crystal for a while, but the loading of the objects and reports took way too long for a automated batch shipping system.
March 17, 2009 at 4:07 pm
Got it. I think we are waiting on another post from G at this point.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 4:12 pm
[font="Verdana"]So what's the make-up of the data in your string? Are words only ever separated by space, or do you want to break on hyphens and control characters as well? Can you get a single word that spans the entire length (like long chemical names)?[/font]
March 17, 2009 at 4:29 pm
[font="Verdana"]Okay, here's what I came up with. I'll wait with baited breath to be told off for using a recursive CTE. 😀
declare @String varchar(max);
declare @MaxLength tinyint;
set @String = 'Hello my name is Jeff. I need some help on a project because right now this is how the application i am working with displays data.';
set @MaxLength = 20;
with
Data as (
select @String as String,
@MaxLength as MaxLength
),
BreakDown as (
select 0 as LineNumber,
MaxLength,
cast('' as varchar(max)) as Line,
String as Remainder
from Data
union all
select LineNumber + 1 as LineNumber,
MaxLength,
substring(Remainder, 1, MaxLength - charindex(' ', reverse(left(Remainder, MaxLength)))) as Line,
substring(Remainder, MaxLength - charindex(' ', reverse(left(Remainder, MaxLength))) + 2, len(Remainder)) as Remainder
from BreakDown
where Remainder <> ''
)
select LineNumber,
Line
from BreakDown
where LineNumber > 0;
[/font]
March 17, 2009 at 9:46 pm
Say it ain't so, Bruce !!! :w00t:
I can't code tonight but my brain is still functioning and I have an idea. Why not use a tally table to get every position where there is a space, then break at each first space above 50 characters? Might have to go through a couple of CTE's to get there, but it seems doable.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 6:59 am
Here is an example of a comment that comes down as one long string.
---0017720610---04---04---03/11/2009--- You can check your order status, track your order, obtain authorization and instructions to return or exchange a product, get a duplicate invoice, access technical support contact information and mail in rebate forms and instructions 24 hours a day on our web site at http://www.******.ca
Heres another......
---0017720612---02---04---03/11/2009--- authorization and instructions to return or exchange a product, get a duplicate invoice, access technical support contact information and mail in rebate forms and instructions 24 hours a day on our web site at http://www.******.ca/help You can check your order status, track your order, obtain authorization and instructions to return or exchange a product, get a duplicate invoice, access technical support contact information and mail in rebate forms and instructions 24 hours a day on our web site at http://www.******.ca
March 18, 2009 at 7:25 am
Bruce W Cassidy (3/17/2009)
[font="Verdana"]Following this with interest. GSquared, did you test your answer? I tried it, but it dropped off the first line for me.BTW, I am wondering why you need to do this in SQL Server. Wouldn't it be better to reformat the string in a reporting tool or front end application?
[/font]
I tested it. Difference is probably that my Numbers table begins with 0 instead of 1. Most people start it with 1. I forgot to mention that.
Basically, my solution is a bunch of stuff stacked on top of "substring". You want multiple substrings, one for each piece of the whole string.
Try this to see how it evolves:
declare @String varchar(max), @Len int;
select @String = '1234567890', @Len = 2;
select substring(@String, number, @Len)
from dbo.Numbers
where number%@Len = 0
and number <= len(@String);
What that does is take every number that's evenly divisible by the value of @Len and run substring on that part of it, up to the length of the string. Vary the string, vary the value of @Len and you get different sized pieces of the string.
What I did from there was use charindex to find the last space before the desired string length and have it cut off there, and use the first space before the start of the substring and have it tack that part onto the beginning.
The Numbers table just gives you a way to turn the pieces into rows based on positions in the string.
In case it matters, here's how I generate the Numbers table:
create table dbo.Numbers (
Number int primary key);
go
insert into dbo.Numbers(Number)
select top 10001 row_number() over (order by t1.object_id)-1
from sys.all_objects t1
cross join sys.all_objects t2;
Creates a Numbers table with all integers between 0 and 10-thousand.
The 0 is important for these positional queries. Can achieve the same thing without it by using "Number-1", but I find that doesn't read as well. When I need to start with 1, I just add something to the Where clause that gives the range I want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 18, 2009 at 7:34 am
jap2bag (3/18/2009)
---0017720610---04---04---03/11/2009--- You can check your order status, track your order, obtain authorization and instructions to return or exchange a product, get a duplicate invoice, access technical support contact information and mail in rebate forms and instructions 24 hours a day on our web site at http://www.******.ca
Heres another......
---0017720612---02---04---03/11/2009--- authorization and instructions to return or exchange a product, get a duplicate invoice, access technical support contact information and mail in rebate forms and instructions 24 hours a day on our web site at http://www.******.ca/help You can check your order status, track your order, obtain authorization and instructions to return or exchange a product, get a duplicate invoice, access technical support contact information and mail in rebate forms and instructions 24 hours a day on our web site at http://www.******.ca[/quote%5D
Just tested it on that and it works. The only change I made to my original script was changing the 50s to 150s.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply