building a broken sequence of numbers as a string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

building a broken sequence of numbers as a string

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-29 : 11:41:57

Jon writes "I am working with image sequences (image_00001.dpx, image_00002.dpx, ..., image_30000.dpx) which combine to make a moving image. Our database is currently designed to work with these sequences as a whole rather than as individual frames. Now I would like to be able to store a sequence string that indicates individual frames without having to store a row of data for each frame.
I would have a stored procedure that receives values which indicate the row to store the data in and the low and high value of a range of images.
Lets say that this stored procedure is called four times with the following values for low and high value:


low  high
11   15
16   30
36   40

Each time the stored procedure is called I want to add the new sequence range into the previous sequence string.
By the forth entry I would like to have generated the string '1-5,11-30,36-40'.


low  high = sequence string
11   15   = '11-15'
16   30   = '11-30'
1     5   = '1-5,11-30'
36   40   = '1-5,11-30,36-40'

Assuming that there are 40 images in this sequence this would indicate that frames 6 thru 10 and frames 31 thru 35 haven't yet been touched.
The question is how can I combine my incoming sequence range with an exsiting sequnce string so that the string stays as compact as possible?

Thanks for your time

Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)""

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-29 : 13:29:35

Did think about this as a single query but think it would get too complicated.
It gives the ranges that are not there so
6-10, 31-35

create table #a(low int, high int)

insert #a select 11, 15
insert #a select 16, 30
insert #a select 1, 5
insert #a select 36, 40

create table #vals (v1 int, v2 int)
insert #vals
select t1.low, high = null from #a t1 left join #a t2 on t1.low - 1 = t2.high where t2.high is null and t1.low <> (select min(low) from #a)
union
select null, t1.high from #a t1 left join #a t2 on t1.high + 1 = t2.low where t2.low is null and t1.high <> (select max(high) from #a)

declare @s varchar(1000)

select @s = coalesce(@s + ', ', '') + convert(varchar(10),v2+1) + '-' + convert(varchar(10),(select min(v1)-1 from #vals t2 where t2.v1 > t1.v2))
from #vals t1
where v2 is not null
select @s

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-29 : 14:19:31

Is this it??

Drop Function dbo.conciseSeq
Go
Create Function dbo.conciseSeq
(
@curSeq nvarchar(1000),
@addSeq nvarchar(100),
@lbound int,
@ubound int
)
Returns nvarchar(1000) As
Begin
Select @curSeq = isnull(@curSeq+',','') + @addSeq

Declare @newSeq nvarchar(1000)

Declare @frameList table (num int primary key)

While len(@curSeq)>0
Begin
if (charindex(',',@curSeq)>0)
Begin
Insert Into @frameList
Select num = number
From
(
Select
lBound = left(curRange,charIndex('-',curRange)-1),
uBound = right(curRange,len(curRange)-charIndex('-',curRange))
From (Select curRange = left(@curSeq,charIndex(',',@curSeq)-1)) as A
) as Z
Inner Join admin.dbo.getSequence(1,40,1) as Y
On Y.Number between Z.lBound and Z.uBound

Set @curSeq = right(@curSeq,len(@curSeq)-charIndex(',',@curSeq))
End
Else
Begin
Insert Into @frameList
Select num = number
From
(
Select
lBound = left(curRange,charIndex('-',curRange)-1),
uBound = right(curRange,len(curRange)-charIndex('-',curRange))
From (Select curRange = @curSeq) as A
) as Z
Inner Join admin.dbo.getSequence(1,40,1) as Y
On Y.Number between Z.lBound and Z.uBound

Set @curSeq = ''
End
End

Select @newSeq = isnull(@newSeq + ',','') + convert(nvarchar,lNum) + '-' + convert(nvarchar,hNum)
From
(
Select
lnum = num,
hnum = (Select top 1 num
From
(
Select num = A.Num, numB = B.num
From @frameList as A
Left Join @frameList as B
On A.num = B.num-1
) as Y
Where numB is null
and Y.num > Z.num)
From
(
Select num = A.Num, numB = B.num
From @frameList as A
Left Join @frameList as B
On A.num = B.num+1
) as Z
Where numB is null
) as Done

Return @newSeq
End
Go

Declare @mySeq nvarchar(1000)

Set @mySeq = '11-15'

Select @mySeq = dbo.conciseSeq(@mySeq,'1-5',1,40)
Select @mySeq
Select @mySeq = dbo.conciseSeq(@mySeq,'16-30',1,40)
Select @mySeq
Select @mySeq = dbo.conciseSeq(@mySeq,'36-40',1,40)
Select @mySeq

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources