March 18, 2009 at 8:09 am
THe only problem i see with this is how can i loop through the results,
Ive tried a cursor, creating a temp table, but it seems it only allows me one go at the data, and time i try to loop through again it says the breakdown object does not exist.
March 18, 2009 at 1:28 pm
jap2bag (3/18/2009)
THe only problem i see with this is how can i loop through the results.
[font="Verdana"]It's not too hard, and you can use a similar idea with GSquared's approach too.
declare @String varchar(max);
declare @MaxLength tinyint;
declare @CommentList table(
CommentNumber int not null,
Line int not null,
LineNumber varchar(100) not null,
primary key (
CommentNumber,
Line
)
);
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 = 75;
with
Data as (
select 1 as CommentNumber,
'---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' as Comment
union all
select 2 as CommentNumber,
'---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' as Comment
),
BreakDown as (
select CommentNumber,
0 as LineNumber,
@MaxLength as MaxLength,
cast('' as varchar(max)) as Line,
cast(Comment as varchar(max)) as Remainder
from Data
union all
select CommentNumber,
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 <> ''
)
insert into @CommentList
select CommentNumber,
LineNumber,
Line
from BreakDown
where LineNumber > 0;
select *
from @CommentList;
[/font]
March 18, 2009 at 2:12 pm
Here's a simple way to get all of them:
if object_id(N'tempdb..#T') is not null
drop table #T;
--
declare @Length int;
--
select @Length = 50;
--
create table #T (
ID int identity primary key,
String varchar(max));
--
insert into #T (String)
select '---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'
union all select '---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'
union all select '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 ID,
case
when charindex(' ', reverse(substring(String, number-@Length, @Length)), 0) > 0
then substring(String, number - charindex(' ', reverse(substring(String, number-@Length, @Length)), 0)+1, charindex(' ', reverse(substring(String, number-@Length, @Length)), 0)-1)
else ''
end +
substring(String, number, @Length - charindex(' ', reverse(substring(String, number, @Length)), 0))
from dbo.Numbers
inner join #T
on Number <= len(String)
and number%@Length = 0;
You can change the value of @Length and get lengths you 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 3:52 pm
Bruce and GSuared, neither of your suggestion work with this test case
DECLARE @String VARCHAR(MAX),
@Size TINYINT
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.',
@Size = 45
;WITH Peso (Part, StartPos, Size)
AS (
SELECT1,
1,
CAST(@Size - CHARINDEX(' ', REVERSE(SUBSTRING(@String, 1, @Size))) AS INT)
UNION ALL
SELECTPart + 1,
StartPos + Size + 1,
CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, StartPos + Size + 1, @Size) + ' ', @Size))) AS INT)
FROMPeso
WHEREStartPos + Size < DATALENGTH(@String)
)
SELECTPart,
SUBSTRING(@String, StartPos, Size)
FROMPeso
ORDER BYPart
N 56°04'39.16"
E 12°55'05.25"
March 18, 2009 at 5:06 pm
[font="Verdana"]Hmmm, does appear to be a bug with my code.
I changed this section:
BreakDown as (
select CommentNumber,
0 as LineNumber,
@MaxLength as MaxLength,
cast('' as varchar(max)) as Line,
ltrim(rtrim(cast(Comment as varchar(max)))) + ' ' as Remainder
from Data
union all
select CommentNumber,
LineNumber + 1 as LineNumber,
MaxLength,
rtrim(substring(Remainder, 1, MaxLength - charindex(' ', reverse(left(Remainder, MaxLength))))) as Line,
ltrim(substring(Remainder, MaxLength - charindex(' ', reverse(left(Remainder, MaxLength))) + 1, len(Remainder))) as Remainder
from BreakDown
where Remainder <> ''
)
That seemed to fix it. I tested it with all three of the examples, and it works fine.
I think I prefer your approach though, Peso, where you're just returning the location within the string. That would make it easier to split on things other than just space (so for example, split on hyphens or tabs or non-breaking spaces or carriage returns or line feeds.)
Should have thought to do that myself. Ah well.
[/font]
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply