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

$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.

$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

$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.

$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.

$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…

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>' + REPLACE(@list, ',', '') + '</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.

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINES.ROUTINE_NAME='ConvertRangesInIntegerList'

AND ROUTINES.ROUTINE_TYPE='function')

DROP FUNCTION dbo.ConvertRangesInIntegerList

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)

BEGIN

DECLARE @Outputlist VARCHAR(MAX);

DECLARE @XMLVersionOfList XML;

DECLARE @MyNumbers TABLE

(

Mynumber INT PRIMARY KEY,

Representation varchar(10),

Rangelength int

);

SELECT @XMLVersionOfList = '<list>' + REPLACE(@list, ',', '') + '</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

If you don’t like the ‘Quirky update’ technique, here is the more conventional approach

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINES.ROUTINE_NAME='SecondConvertRangesInIntegerList'

AND ROUTINES.ROUTINE_TYPE='function')

DROP FUNCTION dbo.SecondConvertRangesInIntegerList

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)

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>' + REPLACE(@list, ',', '') + '</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.

IF EXISTS

(

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINES.ROUTINE_NAME = 'IntegerListfromRangesFunction'

AND ROUTINES.ROUTINE_TYPE = 'function'

)

DROP FUNCTION dbo.IntegerListfromRangesFunction;

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)

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.

/****** 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

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

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>' + REPLACE(@rangelist, ',', '') + '</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

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.

(-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.

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,

);

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.

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,

);

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)

Register for Simple Talk

Lists With, or Without, Ranges in both T-SQL and PowerShell

PowerShell and lists

Processing a simple list

Processing a ranged list

Converting a PowerShell ranged list to a simple list

Converting from a list with ranges to a simple list in PowerShell

Converting from a simple list to a list with ranges in PowerShell

SQL Server

Converting a simple list to a table

Converting from a simple list to a list with ranges in T-SQL in SQL Server

Converting a list with ranges to a simple list to in T-SQL (SQL Server)

Testing

Conclusion

References

About the author

Phil Factor

Phil Factor's contributions

Articles

Books

Top topics

Phil Factor's latest contributions:

AI and Databases

Storing and Retrieving the Initialization and Configuration Data for Applications

Concepts and Issues in Test Data Generation

PowerShell and lists

Processing a simple list

Processing a ranged list

Converting a PowerShell ranged list to a simple list

Converting from a list with ranges to a simple list in PowerShell

Converting from a simple list to a list with ranges in PowerShell

SQL Server

Converting a simple list to a table

Converting from a simple list to a list with ranges in T-SQL in SQL Server

Converting a list with ranges to a simple list to in T-SQL (SQL Server)

Testing

Conclusion

References

Recommended

About the author

Phil Factor

Phil Factor's contributions

Articles

Books

Top topics

Phil Factor's latest contributions:

AI and Databases

Storing and Retrieving the Initialization and Configuration Data for Applications

Concepts and Issues in Test Data Generation