parsing both a range and list into a tmp table

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)

parsing both a range and list into a tmp table

Author

Topic

tinks
Starting Member

34 Posts

Posted - 2003-11-13 : 06:20:48

Hi all

I would like to create a stored proc that populates a temp table with values in a 'range field' that the user can enter. The user can use a '-' to indicate a range or otherwise pass in a comma seperated list.

ie. 1-3,5,8,9 will populate a table with 1,2,3,5,8,9 etc

I can convert a comma seperated list into a table (thanks graz@sqlteam.com) but am having problems with the range part of it ... of course i am trying to stay away from cursors/lengthy do while loops is there any way to do this?

Thanks
Taryn-Vee

Taryn-Vee
@>-'-,---

SmileyConspiracy
Starting Member

8 Posts

Posted - 2003-11-13 : 07:09:48

Is this method any use? It uses a join to a table that contains a list of numbers to work out the full range. One thing you may need to be careful of is that it will not error if the range goes outside the numbers in your list table.

begin tran
declare @SQL varchar(200)
declare @ii int

--create list table
create table #NumberRange( Number int)

--Quick and lazy way to populate NumberRange.
--If you use this process a lot you will probably want to keep NumberRange as a permanent table.
set @ii =1
while @ii <=50
begin
set @SQL = 'insert #NumberRange( Number) values( ' + cast( @ii as varchar) + ')'
exec( @SQL)

set @ii = @ii +1
end

--create test table and data
create table #DataRange( RangeID int identity, Range varchar(10))
insert #DataRange( Range) values( '1-3')
insert #DataRange( Range) values( '11-23')
insert #DataRange( Range) values( '48-55')

--work out range
select d.RangeID, d.range, n.Number
from #DataRange d
inner join #NumberRange n
on n.Number between left( d.range, charindex( '-', d.range) -1) and right( d.range, charindex( '-', reverse( d.range)) -1)

rollback tran

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-13 : 07:16:48

Why not just

begin
-- set @SQL = 'insert #NumberRange( Number) values( ' + cast( @ii as varchar) + ')'
-- exec( @SQL)

insert #NumberRange (Number) values(@ii)

set @ii = @ii +1
end

SmileyConspiracy
Starting Member

8 Posts

Posted - 2003-11-13 : 07:21:21

Doh! Had to work with too much with dynamic SQL lately

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-13 : 08:06:24

Never mind! Quite often I can't recall the simplest sql things.

tinks
Starting Member

34 Posts

Posted - 2003-11-13 : 08:16:46

quote:
Originally posted by Stoad

Never mind! Quite often I can't recall the simplest sql things.

yup - i have been a sql programmer for nigh on 8 years now and this should be simple - but i think my mind sometimes goes blank ...

either way i came up with the following sproc which will parse both a range and comma seperated list into a tmp table ...

---------------------------------------------------------------
Create procedure sp_parseRangeList
( @Array varchar(1000),
@separator char(1) )
AS
-- Some code nicked from graz@sqlteam.com and Stephen Furbank
set nocount on

create table #array
(vals varchar(25)
)

create table #final_array
(vals varchar(25)
)
begin

-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned
declare @arrayval varchar(200)
declare @minval varchar(200)
declare @maxval varchar(200)

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @array = @array + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
insert #array
select left(@array, @separator_position - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
-- select Array_Value = @array_value

-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end
-- insert all single vals
insert #final_array
select * from #array where vals not like '%-%'

-- cursor through all range vals and insert those vals into the final array
declare mycursor cursor for
select * from #array where vals like '%-%'

open mycursor

fetch next from mycursor into @arrayval

while @@fetch_status = 0
begin
-- get the first val
select @separator_position = patindex('%-%' , @arrayval)
select @minval = left(@arrayval, @separator_position - 1)

-- get the last val
select @maxval = ltrim(rtrim(stuff(@arrayval, 1, @separator_position, '')))

while @minval <= @maxval
begin
insert #final_array
select @minval
print @minval
select @minval= @minval + 1
end
fetch next from mycursor into @arrayval

end
-- final select
select * from #final_array
set nocount off
end
go
-------------------------------------------------------------
therefore this will allow you to pass in multi ranges and single vals ie.

exec sp_parseRangeList '100-200,1000-2000,3000,4000,5000',','

Taryn-Vee
@>-'-,---

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-13 : 09:08:55

Great!! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources