In this article, we’ll look at how one would handle lists of numbers with ranges in both SQL Server and PowerShell, converting lists both ways, and showing incidentally how to read and write them.
When a list like ‘1,3,5,6,9’, or ’12 Jan 2016,14 Jan 2016, 18 Feb 2016’ contains a datatype that can be unambiguously sorted in the order of the values of the datatype, it becomes possible to imply a range. This will trim unwieldy lists significantly if they have a lot of contiguous values. ‘1,2,3,4,5’ can be expressed as 1-5 (or 1..5). The starting integer is separated from the end integer in the range by a dash sign. This representation rather clashes with the minus sign, represented by the same symbol, but the comma removes the ambiguity. A number followed immediately by a ‘-‘ means that the ‘-‘is a range symbol. As with SQL’s BETWEEN clause that selects rows, the range includes all the integers in the interval including both endpoints. Because the range syntax is intended to be a more compact form, it is generally only used where there are at least three contiguous values.
A list of integers like this …
-6, -3, -2, -1, 0, 1, 3, 4, 5, 7, 8, 9, 10, 11, 14, 15, 17, 18, 19, 20
Is synonymous with the range expression
-6,-3-1,3-5,7-11,14,15,17-20
When you convert from one format to another, it is usual to remove any duplicates and to correct the order. Lists are essentially ordered. If this ordering has meaning, (such as the order of components in which a part is assembled) the so-called ‘document order’, then ranges aren’t really possible. Lists with ranges are generally sorted in order of ascending value.
In databases, we don’t generally need to worry about interpreting lists of numbers that are stored as datatypes within tables because, if we can safely regard them as ‘atomic’, we aren’t interested in what is inside them, and if they aren’t, they are actually lists of keys, and we store them differently, in tables as separate rows. However, it is quite possible to get them as parameters to a routine, (function or procedure) even though we have table-valued parameters nowadays. When you have a run of integers with many gaps and islands, it is handy to represent them as lists with ranges because they are much more compact to store.
PowerShell and lists
In PowerShell, of course, it is trivial to read a comma-delimited list of integers. Such a list is valid PowerShell. All we need to do is check that the input is valid and execute it. In this example, we square every value in the list
Processing a simple list
|
1 2 3 |
$list = '-3,0,1,4,5,6,7,8,9,11,14,19,20' if ($list -cmatch '(?im)\A(?:[\s]?[-+]?\b\d+\b[\s]?[,]?)+?\z') { Invoke-Expression $list | foreach{ $_ * $_ } } |
Processing a ranged list
If, instead, we are dealing with ranges as well as integers in the same list, the PowerShell is scarcely more difficult. PowerShell understands them already, but using a different syntax. We can very simply modify our previous routine to use ranges. The rather opaque RegEx expression here is merely checking that the list is a valid list and not an attempt to execute dodgy code.
|
1 2 3 |
$list = '-3,0,(1..10),11,14,19,20' if ($list -cmatch '(?im)\A(?:(\(\s?[-+]?\b\d+\b\.\.[-+]?\b\d+\b\s?\)|[-+]?\b\d+\b)[\s,]?)+?\z') { Invoke-Expression $list | foreach{ $_ } | foreach-object{ $_ * $_ } } |
This will work. (The most labour involved was in changing the regex to ensure that what is being executed has correct syntax)
Note that the expanded ranges are passed as arrays, which is why the second foreach-object is needed in the pipeline
Converting a PowerShell ranged list to a simple list
If you just wanted to convert a ranged list that uses the PowerShell syntax into a simple list, then this would do
|
1 2 3 4 5 6 |
$Rangelist = '-3,0,(1..10),11,14,19,20' if ($Rangelist -cmatch '(?im)\A(?:(\(\s?[-+]?\b\d+\b\.\.[-+]?\b\d+\b\s?\)|[-+]?\b\d+\b)[\s,]?)+?\z') { $Array = Invoke-Expression $Rangelist | foreach{ $_ } [string]$Array } |
Converting from a list with ranges to a simple list in PowerShell
So to expand a string list, all we need to do is to convert the conventional range syntax, ‘1-100’, to the PowerShell syntax of ‘(1..100)’ with the complication of the possible minus sign.
|
1 2 3 4 5 6 7 |
$Rangelist = '-6,-3--1,3-5,7-11,14,15,17-20' $Rangelist = $Rangelist -creplace '(?im)\s?([-+]?\b\d+\b)-([-+]?\b\d+\b)\s?', '($1..$2)' if ($Rangelist -cmatch '(?im)\A(?:(\(\s?[-+]?\b\d+\b\.\.[-+]?\b\d+\b\s?\)|[-+]?\b\d+\b)[\s,]?)+?\z') { $Array = Invoke-Expression $Rangelist | foreach{ $_ } [string]$Array } |
Again, the –cmatch operation looks a bit complicated but it is just a precaution you’d want before executing a string.
Converting from a simple list to a list with ranges in PowerShell
To convert from a list of numbers to a range, the reverse of what we’ve achieved, we use a different technique entirely, exploiting a pipeline. This allows us to do two of the most important tasks, taking out duplicates and ensuring that the list is correctly ordered. Having done that, we can examine each number and use a state machine to determine what action you take. It would be considerably easier without having to make a range only with three or more numbers in a sequence. This could be more condensed but might then be tricky to understand.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
$list = '-6,-3,-2,-1,3,4,5,7,8,9,10,11,14,15,17,18,19,20' $state = 'start'; $previous = $null $Array = Invoke-Expression $list | Sort-Object | Get-Unique| &{ Begin { $state = 'start'; }; Process { if ($state -eq 'sequence') #there has been a sequence { if ($_ -ne $previous + 1) { "$start-$previous,"; $state = 'gap' } } elseif ($state -eq 'start') { $state = 'gap' } elseif ($state -eq 'gap') #check for a first in sequence { if ($_ -eq $previous + 1) { $start = $previous; $state = 'possible' } else { "$previous," } } elseif ($state -eq 'possible') #is there a third in the sequence { if ($_ -eq $previous + 1) { $state = 'sequence'; "" } else { $state = 'gap'; "$start,$previous," } } else { throw "This should be impossible (state='$state'" } $previous = $_; }; End { if ($state -eq 'sequence') { "$start-$_" } elseif ($state -eq 'gap') { "$_" } elseif ($state -eq 'possible') { "$start,$previous,$_" } else { throw "No sequence" } }; }; $ofs = ''; [string]$Array |
SQL Server
Converting a simple list to a table
In SQL Server, a fast way of reading a simple list into a table is to do this…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @list VARCHAR(80)-- for our sample list (length as appropriate) SELECT @list='-3,0,1,4,5,6,7,8,9,11,14,19,20'--sample list DECLARE @XMLVersionOfList XML -- we use this to slice up the list DECLARE @MyNumbers TABLE --we will insert it into this table variable ( Mynumber INT PRIMARY KEY ); SELECT @XMLVersionOfList = '<list><i>' + REPLACE(@list, ',', '</i><i>') + '</i></list>'; --this will convert the list of numbers into XML and do some validity-checking. --we put these in a table variable that has a primary key, ready for use --you'd probably want a Try CATCH section here, but not is an example like this INSERT INTO @MyNumbers(Mynumber) SELECT x.y.value('.', 'int') AS IDs FROM @XMLVersionOfList.nodes('/list/i/text()') AS x(y); --this will fail of course if it isn't an integer SELECT MyNumber FROM @MyNumbers |
The only sort of list that isn’t entirely foreign to SQL Server is the XML list, so we have converted a simple comma-delimited list into an XML list and thence into SQL Server, ending up with a table variable containing all the integers in a column. Because this is the primary key, we can guarantee that they are unique.
Converting from a simple list to a list with ranges in T-SQL in SQL Server
With this technique of creating a table we can now convert a list of integers to the list with ranges. One can do this with window functions but it is complicated by the condition that a pair of numbers in sequence don’t merit a range, only three or more. I’ve chosen first a quirky update, as it was faster.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINES.ROUTINE_NAME='ConvertRangesInIntegerList' AND ROUTINES.ROUTINE_TYPE='function') DROP FUNCTION dbo.ConvertRangesInIntegerList go CREATE FUNCTION ConvertRangesInIntegerList ( -- the simple list. The list doesn't have to be in sorted order. @list VARCHAR(MAX) --eg. 1,2,3,4,6,8,9,10,11,13,21,22,23,25,27,28,30 --which would become 1-4,6,8-11,13,21-23,25,27,28,30 --note that 27,28 are not converted to 27-28 because the range isn't sufficient --SELECT dbo.ConvertRangesInIntegerList ('1,2,3,4,6,8,9,10,11,13,21,22,23,25,27,28,30') ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Outputlist VARCHAR(MAX); DECLARE @XMLVersionOfList XML; DECLARE @MyNumbers TABLE ( Mynumber INT PRIMARY KEY, Representation varchar(10), Rangelength int ); SELECT @XMLVersionOfList = '<list><i>' + REPLACE(@list, ',', '</i><i>') + '</i></list>'; --this will convert the list of numbers into XML and do some validity-checking. DECLARE @PreviousValue INT, @RowRepresentation varchar(10), @RangeLength INT SELECT @RangeLength=0; --we put these in a table variable that has a primary key, ready for use --you'd probably want a Try CATCH section here, but not is an example like this INSERT INTO @MyNumbers(Mynumber) SELECT DISTINCT x.y.value('.', 'int') AS IDs FROM @XMLVersionOfList.nodes('/list/i/text()') AS x(y); --now we update this quirkily to detect gaps in the sequence and represent islands UPDATE @MyNumbers SET @RowRepresentation=[@MyNumbers].Representation= CASE WHEN [@MyNumbers].Mynumber=@PreviousValue+1 --next in the sequence? THEN '' WHEN @RowRepresentation='' AND @RangeLength>1--the previous row was in a sequence but this isn't THEN '-'+CONVERT(varCHAR(5),@PreviousValue)+','+CONVERT(varCHAR(5),[@MyNumbers].Mynumber) ELSE CASE WHEN @rangelength=1 THEN ','+CONVERT(varCHAR(5),@PreviousValue) ELSE '' END + ','+CONVERT(varCHAR(5),[@MyNumbers].Mynumber) END,--else it was not in a sequence @RangeLength=[@MyNumbers].Rangelength= CASE WHEN [@MyNumbers].Mynumber=@PreviousValue+1 THEN @RangeLength+1 ELSE 0 END, @PreviousValue=[@MyNumbers].Mynumber --remember the value for the next row SET @Outputlist='' --now join together all the representations SELECT @Outputlist=@Outputlist+Representation FROM @MyNumbers RETURN STUFF(--trim the start and add the last entry if it ended with a sequence @Outputlist+CASE WHEN @RowRepresentation='' THEN '-'+CONVERT(varCHAR(5),@PreviousValue) ELSE '' END, 1,1,'') END GO |
If you don’t like the ‘Quirky update’ technique, here is the more conventional approach
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINES.ROUTINE_NAME='SecondConvertRangesInIntegerList' AND ROUTINES.ROUTINE_TYPE='function') DROP FUNCTION dbo.SecondConvertRangesInIntegerList go CREATE FUNCTION SecondConvertRangesInIntegerList ( -- the simple list. The list doesn't have to be in sorted order. @list VARCHAR(MAX) --eg. 1,2,3,4,6,8,9,10,11,13,21,22,23,25,27,28,30 --which would become 1-4,6,8-11,13,21-23,25,27,28,30 --note that 27,28 are not converted to 27-28 because the range isn't sufficient --SELECT dbo.SecondConvertRangesInIntegerList ('1,2,3,4,6,8,9,10,11,13,21,22,23,25,27,28,30') ) RETURNS VARCHAR(MAX) AS Begin DECLARE @XMLVersionOfList XML; -- we use this to slice up the list DECLARE @MyNumbers TABLE --we will insert it into this table variable ( rank INT IDENTITY(1, 1), number INT PRIMARY KEY ); DECLARE @ranks TABLE ( theMin INT, TheMax INT, TheCount INT ); SELECT @XMLVersionOfList = '<list><i>' + REPLACE(@list, ',', '</i><i>') + '</i></list>'; --this will convert the list of numbers into XML and do some validity-checking. --we put these in a table variable that has a primary key, ready for use INSERT INTO @MyNumbers(number) SELECT x.y.value('.', 'int') AS IDs FROM @XMLVersionOfList.nodes('/list/i/text()') AS x(y); --this will fail of course if it isn't an integer -- now we group into sequences INSERT INTO @ranks(theMin, TheMax, TheCount) SELECT MIN([@MyNumbers].number), MAX([@MyNumbers].number), COUNT(*) FROM @MyNumbers GROUP BY [@MyNumbers].number - [@MyNumbers].rank; -- we can now create the ranged list. We need to check whether the sequence -- was less than three long. Otherwise it would be simple. DECLARE @Outputlist VARCHAR(MAX); SELECT @Outputlist = ''; SELECT @Outputlist = @Outputlist+ CASE WHEN ThisRowsRange.TheCount IS NULL THEN CASE WHEN PreviousRowsRange.TheCount < 3 THEN ','+CONVERT(VARCHAR(5),ThisRow.number) ELSE '' END WHEN ThisRowsRange.TheCount<3 THEN ','+CONVERT(VARCHAR(5),ThisRow.number) ELSE ','+CONVERT(VARCHAR(5),ThisRowsRange.theMin) +'-'+CONVERT(VARCHAR(5),ThisRowsRange.TheMax) END FROM @MyNumbers ThisRow LEFT OUTER JOIN @MyNumbers PreviousRow ON ThisRow.rank = PreviousRow.rank + 1 LEFT OUTER JOIN @ranks ThisRowsRange ON ThisRow.number = ThisRowsRange.theMin LEFT OUTER JOIN @ranks PreviousRowsRange ON PreviousRow.number = PreviousRowsRange.theMin ORDER BY ThisRow.rank; RETURN STUFF(@Outputlist, 1, 1, ''); End |
So now we are half way to success. All we have to do now is to unwrap a list containing ranges!
Converting a list with ranges to a simple list to in T-SQL (SQL Server)
There is a simple iterative approach to doing this that is fine with small ranges, but it is likely to run out of puff with the sort of lists and ranges you could be faced with.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINES.ROUTINE_NAME = 'IntegerListfromRangesFunction' AND ROUTINES.ROUTINE_TYPE = 'function' ) DROP FUNCTION dbo.IntegerListfromRangesFunction; GO CREATE FUNCTION IntegerListfromRangesFunction ( /** summary: > This Procedure takes a list of integers with ranges in them and turns it into a list of integers. Effectively, it removes the ranges by turning them into all the integers in the range. This uses the iterative solution without an intermediary table Author: Phil Factor Revision: 1.0 date: 20 Nov 2016 example: > -eg. SELECT dbo.IntegerListfromRangesFunction('1-4,6,8-11,13,21-23,25,27-28,30') --which would become 1,2,3,4,6,8,9,10,11,13,21,22,23,25,27,28,30 returns: > The list as an ascll string representing a list of integers. **/ @list VARCHAR(MAX) --eg. 1-4,6,8-11,13,21-23,25,27-28,30 ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @c CHAR, --the current character being examined @ii INT, --the iteration count @len INT, --length of the list @negative BIT, --is this a negative number, @Accumulator INT, --the value of the number so far @ThereIsAnRvalue INT, --Is there an Rvalue in the accumulator @previous CHAR, --the previous character examined @lvalue INT, --left hand side value of ecxpression, @OutputList VARCHAR(MAX); -- the output list --so initialise everything SELECT @list = @list + ',', @ii = 1, @len = LEN(@list), @negative = 0, @Accumulator = 0, @previous = ',', @lvalue = 0, @OutputList = '', @ThereIsAnRvalue = 0; WHILE(@ii <= @len) --from the start to end of the list BEGIN SELECT @c = SUBSTRING(@list, @ii, 1); --get the next character IF @c = '-' AND @previous LIKE '[0-9]' --is it the range keyword SELECT @lvalue = --if it is, then save the lvalue CASE WHEN @negative = 1 THEN -@Accumulator ELSE @Accumulator END, @ThereIsAnRvalue = 1, @Accumulator = 0, @negative = 0; ELSE IF @c = '-' --it IS a negative number following SELECT @negative = 1; ELSE IF @c LIKE '[0-9]' --it is a number SELECT @Accumulator = (@Accumulator * 10) + @c; ELSE IF(@c = ',') --it is the end of the current list entry BEGIN SELECT @Accumulator = CASE WHEN @negative = 1 THEN -@Accumulator ELSE @Accumulator END; IF @ThereIsAnRvalue = 1 WHILE(@lvalue <= @Accumulator) BEGIN SELECT @OutputList = @OutputList + ',' + CONVERT(VARCHAR(5), @lvalue); SELECT @lvalue = @lvalue + 1; END; ELSE SELECT @OutputList = @OutputList + ',' + CONVERT(VARCHAR(5), @Accumulator); SELECT @Accumulator = 0, @negative = 0, @lvalue = 0, @ThereIsAnRvalue = 0; END; SELECT @ii = @ii + 1, @previous = @c; END; RETURN STUFF(@OutputList, 1, 1, ''); END; |
SQL has the syntax to cope with ranges, in the BETWEEN sub clause of the WHERE clause. The IN subclause also uses simple lists. What we need, therefore, to do is to do an inner join with a number table to get that useful table full of the members of the list. We finish off by converting that table column back into a list. The downside is that this means we can’t use a function because the use of INSERT..EXEC isn’t allowed in a function because the query optimiser can’t be sure that it would be deterministic. We have to make it a procedure. The disadvantage is therefore in that we can’t use it for a whole table of lists. In that case, the iterative method wins out. In this example, I create a number table quickly on the fly, so it is only useful for the range I’ve specified for the table. If you were to use this method, you’d probable use a permanent number table with the required range.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
/****** Object: StoredProcedure [dbo].[IntegerListfromRanges] Script Date: 01/12/2016 10:45:21 ******/ IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINES.ROUTINE_TYPE='procedure' AND ROUTINES.ROUTINE_SCHEMA='dbo' AND ROUTINES.ROUTINE_NAME = 'IntegerListfromRanges') DROP PROCEDURE IntegerListfromRanges go CREATE procedure [dbo].[IntegerListfromRanges] /** summary: > This Procedure takes a list of integers with ranges in them and turns it into a list of integers. Effectively, it removes the ranges by turning them into all the integers in the range. This is a demonstration of a working method because I create a number table on the fly with negative integers (these make things harder) and normally you'd just want to have the table of numbers that you would then use for whatever relational task you were engaged with Author: Phil Factor Revision: 1.0 date: 19 Nov 2016 example: > DECLARE @list VARCHAR(MAX) EXECUTE dbo.IntegerListFromRanges '-12--3,4,6,7',@list OUTPUT SELECT @list returns: > The list as an ascll string representing a list of integers. **/ @Rangelist VARCHAR(MAX), --eg. 1-4,6,8-11,13,21-23,25,27-28,30 --which would become 1,2,3,4,6,8,9,10,11,13,21,22,23,25,27,28,30 @list VARCHAR(MAX) output AS BEGIN --declare some variables DECLARE @inClause VARCHAR(MAX), @BetweenClause VARCHAR(MAX), @XMLVersionOfList XML,@NumberTableSQL NVARCHAR(MAX) DECLARE @MyNumbers TABLE ( MyRange VARCHAR(10) ); --here we create the numbers table with a precautionary test IF NOT EXISTS( SELECT TABLES.TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLES.TABLE_NAME LIKE '#NumbersForRanges%') BEGIN SELECT TOP 10000 IDENTITY(int,-5000,1) AS Number INTO #NumbersForRanges FROM sys.objects s1 CROSS JOIN sys.objects s2; ALTER TABLE #NumbersForRanges ADD CONSTRAINT PK_NumbersForRanges PRIMARY KEY CLUSTERED (Number); end --we convert the list into XML SELECT @XMLVersionOfList = '<list><i>' + REPLACE(@rangelist, ',', '</i><i>') + '</i></list>'; --we now take each component and insert it into a row of a table variable INSERT INTO @MyNumbers(MyRange) SELECT x.y.value('.', 'varchar(10)') AS IDs FROM @XMLVersionOfList.nodes('/list/i/text()') AS x(y); -- end of section converting list to a table we can work with SELECT @inClause='', @BetweenClause='' --we want to make a SELECT statement against a number table SELECT @inClause=@inClause +CASE WHEN MyRange like '%[0-9]-%' THEN '' ELSE ',' + MyRange END, @BetweenClause=@BetweenClause +CASE WHEN MyRange like '%[0-9]-%' THEN 'or (number between '+STUFF(MyRange,PATINDEX('%[0-9]-%',[@MyNumbers].MyRange)+1,1,' and ')+') ' ELSE'' END FROM @MyNumbers SELECT @list='' IF @@RowCount>0 BEGIN SELECT @NumberTableSQL='SELECT @OutputList=''''; SELECT @OutputList=@OutputList+ '',''+ convert(varchar(5),number) from #NumbersForRanges where ' + CASE WHEN LEN(@inClause)>0 THEN 'number in ('+ STUFF(@inClause,1,1,0)+') 'ELSE '' END+ CASE WHEN LEN(@BetweenClause)>0 THEN CASE WHEN LEN(@inClause)>0 THEN 'or' ELSE '' END+ ' ('+ STUFF(@BetweenClause,1,3,'')+')'ELSE '' END; ---now we execute the code against the number table EXEC sp_ExecuteSQL @NumberTableSQL, N'@OutputList Varchar(max) OUTPUT',@OutputList=@List OUTPUT SELECT @list=STUFF(@list,1,1,'') END end go |
Testing
With any programming, the most difficult part is working out how to test what you’ve done for accuracy and performance. As well as the basic assertion tests, I needed, perhaps, ten thousand or so lists in a SQL Server table as a unit test. I felt pretty sure that a good scheme was to convert them to ranged form, convert them back again, and then compare. I then used SQL Data Generator with the following very simple reverse-regex to add ten thousand rows in the ‘Before’ column.
|
1 |
(-20,)(-19,)?(-18,)(-17,)?(-16,)(-15,)?(-14,)(-13,)?(-12,)(-11,)?(-10,)(-9,)?(-8,)(-7,)?(-6,)(-5,)?(-4,)(-3,)?(-2,)(-1,)?(0,)(1,)?(2,)(3,)?(4,)(5,)?(6,)(7,)?(8,)(9,)?(10,)(11,)?(12,)(13,)?(14,)(15,)?(16,)(17,)?(18,)(19,)?(20,)(21,)(22,)?(23,)(24,)?(25,)(26,)?(27,)(28,)?(29,)(30,)?(31,)(32,)?(33,)(34,)?(35,)(36,)?(37,)(38,)?(39,)(40,)?(41,)(42,)?(43,)(44,)?(45,)(46,)?(47,)(48,)?(49,)(50,)?(51,)(52,)?(53,)(54,)?(55,)(56,)?(57,)(58,)?(59,)(60,)?61 |
As well as making performance testing easier , it was able to flush out some rather more subtle bugs.
Here was the code I used for the test when using the function for getting the integer list from the ranged list.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
IF ( --there were any cases where the two conversions to and from ranges didnt match the original SELECT SUM( CASE WHEN list<> dbo.IntegerListfromRangesFunction( dbo.ConvertRangesInIntegerList(list)) THEN 1 ELSE 0 END) FROM dbo.listsOfNumbers )>0 RAISERROR( 'there were discrepencies in the ranges before and after conversion', 16, 1 ); |
Which compared the original unranged list into a list with ranges, and then back again, comparing it with the original. When we try to do the same with the stored procedure version, we have to resort to RBAR which is so slow that you can nip out for a cuppa and a chat while it is doing it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
SET NOCOUNT ON; --delete the temp table if it exists IF EXISTS (SELECT TABLES.TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLES.TABLE_NAME LIKE '#templist%' ) DROP TABLE #templist; --create the table from the test ranges SELECT IDENTITY(INT, 1, 1) AS range_id, list AS list, dbo.ConvertRangesInIntegerList(list) AS ranged, CONVERT(VARCHAR(2000), '') AS after --we will insert into this INTO #tempList FROM dbo.listsOfNumbers; --make iterative stuff faster ALTER TABLE #tempList ADD CONSTRAINT PK_Identity PRIMARY KEY CLUSTERED(range_id); --now RBAR through, reversing the conversion and putting it in the third column DECLARE @ii INT, @iiMax INT, @Ranged VARCHAR(8000), @UnRanged VARCHAR(8000); SELECT @ii = 1, @iiMax = COUNT(*) FROM #tempList; WHILE @ii <= @iiMax BEGIN SELECT @Ranged = #tempList.ranged FROM #tempList WHERE #tempList.range_id = @ii; EXECUTE dbo.IntegerListfromRanges @Ranged, @UnRanged OUTPUT; UPDATE #tempList SET #tempList.after = @UnRanged WHERE #tempList.range_id = @ii; SELECT @ii = @ii + 1; END; --now check the two IF EXISTS (SELECT * FROM #tempList WHERE #tempList.list <> #tempList.after) RAISERROR( 'there were discrepencies in the ranges before and after conversion', 16, 1 ); SET NOCOUNT OFF; |
Conclusion
I must admit to find myself doing odd things in SQL Server occasionally. It is obvious, from what I’ve shown you here that operations like these are much easier in a language such as C# or PowerShell, yet sometimes it is just handy to be able to do them in SQL Server. I confess that string lists of integers fascinate me a bit because they can prove to be useful to communicate with applications, but up to now, I’ve always shied away from encouraging ranges. Now that one comes across ranges more often, thanks to BI, it is nice to be sure that, in or out of the database, ranged lists can be coped with. As always, I’d be delighted to be told of an easier, more convenient or quicker was of doing this in SQL Server. It is certainly nice enough to know that it can be done.
References
- Range expansion in lists (RosettaCode)
- Range Extraction in lists (RosettaCode)
- Xquery sequences (Wikibooks)
Load comments