\u00a0In this article, we\u2019ll 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. \u00a0<\/p>\n
\u00a0When a \u00a0list like \u20181,3,5,6,9\u2019, or \u201912 Jan 2016,14 Jan 2016, 18 Feb 2016\u2019 \u00a0contains 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. \u00a0\u20181,2,3,4,5\u2019 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 \u2018-\u2018 means that the \u2018-\u2018is a range symbol. \u00a0As with SQL\u2019s 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,\u00a0 it is generally only used where there are at least three contiguous values.<\/p>\n
A list of integers like this \u2026<\/p>\n
Is synonymous with the range expression<\/p>\n When you convert from one format to another, it is usual to remove any duplicates and to correct the order. \u00a0Lists are essentially ordered. If this ordering has meaning, (such as the order of components in which a part is assembled) the so-called \u2018document order\u2019, then ranges aren\u2019t really possible. Lists with ranges are generally sorted in order of ascending value.<\/p>\n \u00a0In databases, we don\u2019t generally need to worry about interpreting lists of numbers that are stored as datatypes within tables because, if we can safely regard them as \u2018atomic\u2019, we aren\u2019t interested in what is inside them, and if they aren\u2019t, 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.<\/p>\n 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<\/p>\n 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.<\/p>\n This will work. (The most labour involved was in changing the regex to ensure that what is being executed has correct syntax)<\/p>\n Note that the expanded ranges are passed as arrays, which is why the second foreach-object<\/strong> \u00a0is needed in the pipeline<\/p>\n If you just wanted to convert a ranged list that uses the PowerShell syntax \u00a0into a simple list, then this would do<\/p>\n So to expand a string list, all we need to do is to convert the conventional range syntax, \u20181-100\u2019, to the PowerShell syntax of \u2018(1..100)\u2019 with the complication of the possible minus sign.<\/p>\n Again, the \u2013cmatch<\/strong> operation looks a bit complicated but it is just a precaution you\u2019d want before executing a string.<\/p>\n To convert from a list of numbers to a range, the reverse of what we\u2019ve 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.\u00a0 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.<\/p>\n In SQL Server, a fast way of reading a simple list into a table is to do this\u2026<\/p>\n The only sort of list that isn\u2019t 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.<\/p>\n With this technique of creating a table we can now convert a list of integers to \u00a0the 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\u2019t merit a range, only three or more. I\u2019ve chosen first \u00a0a quirky update, as it was faster.<\/p>\n If you don’t like the ‘Quirky update’ technique, here is the more conventional approach<\/p>\n So now we are half way to success. All we have to do now is to unwrap a list containing ranges!<\/p>\n 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\u00a0 lists and ranges you could be faced with.<\/p>\n 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\u2019t use a function because the use of INSERT..EXEC isn\u2019t allowed in a function because the query optimiser can\u2019t be sure that it would be deterministic. We have to make it a procedure. The disadvantage is therefore in that we can\u2019t 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\u2019ve specified for the table. If you were to use this method, you\u2019d probable use a permanent number table with the required range.<\/p>\n 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<\/a> with \u00a0the following very simple reverse-regex to add ten thousand rows in the ‘Before’ column.<\/p>\n As well as making performance testing easier , it was able to flush out some rather more subtle bugs.\u00a0<\/p>\n Here was the code I used for the test when using the function for getting the integer list from the ranged list.<\/p>\n 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.<\/p>\n 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, \u00a0I\u2019ve 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\u2019d 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.<\/p>\n Whether you are working in a procedural language like PowerShell or in T-SQL, there is something slightly bothersome about having to deal with parameters that are lists, or worse with ranges amongst the values. In fact, once you have a way of dealing with them, they can be convenient, especially when bridging the gulf between application and the database. Phil Factor shows how to deal with them.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[35,143531],"tags":[],"coauthors":[6813],"class_list":["post-69155","post","type-post","status-publish","format-standard","hentry","category-powershell","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69155","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=69155"}],"version-history":[{"count":17,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69155\/revisions"}],"predecessor-version":[{"id":92502,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69155\/revisions\/92502"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=69155"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=69155"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=69155"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=69155"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}-6, -3, -2, -1, 0, 1, 3, 4, 5, 7, 8, 9, 10, 11, 14, 15, 17, 18, 19, 20<\/code><\/p>\n-6,-3-1,3-5,7-11,14,15,17-20<\/code><\/p>\nPowerShell and lists<\/h1>\n
Processing a simple list<\/h2>\n
$list = '-3,0,1,4,5,6,7,8,9,11,14,19,20'\r\nif ($list -cmatch '(?im)\\A(?:[\\s]?[-+]?\\b\\d+\\b[\\s]?[,]?)+?\\z')\r\n\u00a0 { Invoke-Expression $list | foreach{ $_ * $_ } }<\/pre>\nProcessing a ranged list<\/h2>\n
$list = '-3,0,(1..10),11,14,19,20'\r\nif ($list -cmatch '(?im)\\A(?:(\\(\\s?[-+]?\\b\\d+\\b\\.\\.[-+]?\\b\\d+\\b\\s?\\)|[-+]?\\b\\d+\\b)[\\s,]?)+?\\z')\r\n\u00a0 { Invoke-Expression $list | foreach{ $_ } | foreach-object{ $_ * $_ } }<\/pre>\nConverting a PowerShell ranged list to a simple list<\/h2>\n
$Rangelist = '-3,0,(1..10),11,14,19,20'\r\nif ($Rangelist -cmatch '(?im)\\A(?:(\\(\\s?[-+]?\\b\\d+\\b\\.\\.[-+]?\\b\\d+\\b\\s?\\)|[-+]?\\b\\d+\\b)[\\s,]?)+?\\z')\r\n{\r\n\u00a0\u00a0\u00a0 $Array = Invoke-Expression $Rangelist | foreach{ $_ }\r\n\u00a0\u00a0\u00a0 [string]$Array\r\n}<\/pre>\nConverting from a list with ranges to a simple list in PowerShell<\/h2>\n
$Rangelist = '-6,-3--1,3-5,7-11,14,15,17-20'\r\n$Rangelist = $Rangelist -creplace '(?im)\\s?([-+]?\\b\\d+\\b)-([-+]?\\b\\d+\\b)\\s?', '($1..$2)'\r\nif ($Rangelist -cmatch '(?im)\\A(?:(\\(\\s?[-+]?\\b\\d+\\b\\.\\.[-+]?\\b\\d+\\b\\s?\\)|[-+]?\\b\\d+\\b)[\\s,]?)+?\\z')\r\n{\r\n\u00a0\u00a0\u00a0 $Array = Invoke-Expression $Rangelist | foreach{ $_ }\r\n\u00a0\u00a0\u00a0 [string]$Array\r\n}<\/pre>\nConverting from a simple list to a list with ranges in PowerShell<\/h2>\n
$list = '-6,-3,-2,-1,3,4,5,7,8,9,10,11,14,15,17,18,19,20'\r\n$state = 'start'; $previous = $null\r\n$Array = Invoke-Expression $list | Sort-Object | Get-Unique| &{\r\n\u00a0\u00a0\u00a0 Begin { $state = 'start'; };\r\n\u00a0\u00a0\u00a0 Process\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if ($state -eq 'sequence') #there has been a sequence\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 { if ($_ -ne $previous + 1) { \"$start-$previous,\"; $state = 'gap' } }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 elseif ($state -eq 'start') { $state = 'gap' }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 elseif ($state -eq 'gap') #check for a first in sequence\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 { if ($_ -eq $previous + 1) { $start = $previous; $state = 'possible' }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else { \"$previous,\" } }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 elseif ($state -eq 'possible') #is there a third in the sequence\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if ($_ -eq $previous + 1) { $state = 'sequence'; \"\" }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else { $state = 'gap'; \"$start,$previous,\" }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else { throw \"This should be impossible (state='$state'\" }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $previous = $_;\r\n\u00a0\u00a0\u00a0 };\r\n\u00a0\u00a0\u00a0 End\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if ($state -eq 'sequence') { \"$start-$_\" }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 elseif ($state -eq 'gap') { \"$_\" }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 elseif ($state -eq 'possible') { \"$start,$previous,$_\" }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else { throw \"No sequence\" }\r\n\u00a0\u00a0\u00a0 };\r\n};\r\n\r\n$ofs = ''; [string]$Array<\/pre>\nSQL Server<\/h1>\n
Converting \u00a0a simple list to a table<\/h2>\n
DECLARE @list VARCHAR(80)-- for our sample list (length as appropriate)\r\nSELECT @list='-3,0,1,4,5,6,7,8,9,11,14,19,20'--sample list\r\nDECLARE @XMLVersionOfList XML -- we use this to slice up the list\r\nDECLARE @MyNumbers TABLE --we will insert it into this table variable\r\n\u00a0 (\r\n\u00a0\u00a0\u00a0 Mynumber INT PRIMARY KEY\r\n);\r\nSELECT @XMLVersionOfList = '<list><i>' + REPLACE(@list, ',', '<\/i><i>') + '<\/i><\/list>';\r\n--this will convert the list of numbers into XML and do some validity-checking.\r\n--we put these in a table variable that has a primary key, ready for use\r\n--you'd probably want a Try CATCH section here, but not is an example like this\r\nINSERT INTO @MyNumbers(Mynumber)\r\n\u00a0 SELECT x.y.value('.', 'int') AS IDs\r\n\u00a0 FROM @XMLVersionOfList.nodes('\/list\/i\/text()') AS x(y);\r\n--this will fail of course if it isn't an integer\r\nSELECT MyNumber FROM @MyNumbers\r\n\r\n<\/pre>\nConverting from a simple list to a list with ranges in T-SQL in SQL Server<\/h2>\n
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES\r\n\u00a0 WHERE ROUTINES.ROUTINE_NAME='ConvertRangesInIntegerList'\r\n\u00a0\u00a0\u00a0 AND ROUTINES.ROUTINE_TYPE='function')\r\nDROP FUNCTION dbo.ConvertRangesInIntegerList\r\ngo\r\nCREATE FUNCTION ConvertRangesInIntegerList\r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- the simple list. The list doesn't have to be in sorted order.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @list VARCHAR(MAX) --eg. 1,2,3,4,6,8,9,10,11,13,21,22,23,25,27,28,30\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --which would become\u00a0\u00a0\u00a0\u00a0 1-4,6,8-11,13,21-23,25,27,28,30\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --note that 27,28 are not converted to 27-28 because the range isn't sufficient\r\n--SELECT dbo.ConvertRangesInIntegerList ('1,2,3,4,6,8,9,10,11,13,21,22,23,25,27,28,30')\r\n)\r\nRETURNS VARCHAR(MAX)\r\nAS\r\nBEGIN\r\nDECLARE @Outputlist VARCHAR(MAX);\r\nDECLARE @XMLVersionOfList XML;\r\nDECLARE @MyNumbers TABLE\r\n\u00a0 (\r\n\u00a0\u00a0\u00a0 Mynumber INT PRIMARY KEY,\r\n\u00a0\u00a0\u00a0 Representation varchar(10),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Rangelength int\r\n);\r\nSELECT @XMLVersionOfList = '<list><i>' + REPLACE(@list, ',', '<\/i><i>') + '<\/i><\/list>';\r\n--this will convert the list of numbers into XML and do some validity-checking.\r\nDECLARE @PreviousValue INT, @RowRepresentation varchar(10), @RangeLength INT\r\nSELECT @RangeLength=0;\r\n--we put these in a table variable that has a primary key, ready for use\r\n--you'd probably want a Try CATCH section here, but not is an example like this\r\nINSERT INTO @MyNumbers(Mynumber)\r\n\u00a0 SELECT DISTINCT x.y.value('.', 'int') AS IDs\r\n\u00a0 FROM @XMLVersionOfList.nodes('\/list\/i\/text()') AS x(y);\r\n--now we update this quirkily to detect gaps in the sequence and represent islands\r\nUPDATE @MyNumbers\r\n\u00a0 SET\r\n\u00a0\u00a0 @RowRepresentation=[@MyNumbers].Representation=\r\n\u00a0\u00a0\u00a0 CASE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 WHEN [@MyNumbers].Mynumber=@PreviousValue+1 --next in the sequence?\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 THEN ''\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN @RowRepresentation='' AND @RangeLength>1--the previous row was in a sequence but this isn't\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 THEN '-'+CONVERT(varCHAR(5),@PreviousValue)+','+CONVERT(varCHAR(5),[@MyNumbers].Mynumber)\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE CASE WHEN @rangelength=1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN ','+CONVERT(varCHAR(5),@PreviousValue) ELSE '' END\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 + ','+CONVERT(varCHAR(5),[@MyNumbers].Mynumber) END,--else it was not in a sequence\r\n\u00a0\u00a0 @RangeLength=[@MyNumbers].Rangelength=\r\n\u00a0\u00a0\u00a0 CASE WHEN [@MyNumbers].Mynumber=@PreviousValue+1 THEN @RangeLength+1 ELSE 0 END,\r\n\u00a0\u00a0 @PreviousValue=[@MyNumbers].Mynumber --remember the value for the next row\r\nSET @Outputlist='' --now join together all the representations\r\nSELECT @Outputlist=@Outputlist+Representation FROM @MyNumbers\r\nRETURN\r\n\u00a0 STUFF(--trim the start and add the last entry if it ended with a sequence\r\n\u00a0\u00a0 @Outputlist+CASE WHEN @RowRepresentation='' THEN '-'+CONVERT(varCHAR(5),@PreviousValue) ELSE '' END,\r\n\u00a0\u00a0 1,1,'')\r\nEND\r\nGO\r\n<\/pre>\nIF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES\r\n WHERE ROUTINES.ROUTINE_NAME='SecondConvertRangesInIntegerList'\r\n AND ROUTINES.ROUTINE_TYPE='function')\r\nDROP FUNCTION dbo.SecondConvertRangesInIntegerList\r\ngo\r\nCREATE FUNCTION SecondConvertRangesInIntegerList\r\n(\r\n -- the simple list. The list doesn't have to be in sorted order.\r\n @list VARCHAR(MAX) --eg. 1,2,3,4,6,8,9,10,11,13,21,22,23,25,27,28,30\r\n --which would become 1-4,6,8-11,13,21-23,25,27,28,30\r\n --note that 27,28 are not converted to 27-28 because the range isn't sufficient\r\n--SELECT dbo.SecondConvertRangesInIntegerList ('1,2,3,4,6,8,9,10,11,13,21,22,23,25,27,28,30')\r\n)\r\nRETURNS VARCHAR(MAX)\r\nAS\r\nBegin\r\nDECLARE @XMLVersionOfList XML; -- we use this to slice up the list\r\nDECLARE @MyNumbers TABLE --we will insert it into this table variable\r\n (\r\n rank INT IDENTITY(1, 1),\r\n number INT PRIMARY KEY\r\n);\r\nDECLARE @ranks TABLE\r\n (\r\n theMin INT,\r\n TheMax INT,\r\n TheCount INT\r\n);\r\nSELECT @XMLVersionOfList =\r\n '<list><i>' + REPLACE(@list, ',', '<\/i><i>') + '<\/i><\/list>';\r\n--this will convert the list of numbers into XML and do some validity-checking.\r\n--we put these in a table variable that has a primary key, ready for use\r\nINSERT INTO @MyNumbers(number)\r\n SELECT x.y.value('.', 'int') AS IDs\r\n FROM @XMLVersionOfList.nodes('\/list\/i\/text()') AS x(y);\r\n--this will fail of course if it isn't an integer\r\n-- now we group into sequences \r\nINSERT INTO @ranks(theMin, TheMax, TheCount)\r\n SELECT MIN([@MyNumbers].number), MAX([@MyNumbers].number), COUNT(*)\r\n FROM @MyNumbers\r\n GROUP BY [@MyNumbers].number - [@MyNumbers].rank;\r\n-- we can now create the ranged list. We need to check whether the sequence\r\n-- was less than three long. Otherwise it would be simple.\r\nDECLARE @Outputlist VARCHAR(MAX);\r\nSELECT @Outputlist = '';\r\nSELECT @Outputlist = @Outputlist+\r\n CASE \r\n WHEN ThisRowsRange.TheCount IS NULL THEN \r\n CASE WHEN PreviousRowsRange.TheCount < 3 \r\n\t THEN ','+CONVERT(VARCHAR(5),ThisRow.number) ELSE '' \r\n\t END \r\n\tWHEN ThisRowsRange.TheCount<3 THEN ','+CONVERT(VARCHAR(5),ThisRow.number)\r\n\tELSE ','+CONVERT(VARCHAR(5),ThisRowsRange.theMin)\r\n\t +'-'+CONVERT(VARCHAR(5),ThisRowsRange.TheMax) \r\n END\r\n FROM @MyNumbers ThisRow\r\n LEFT OUTER JOIN @MyNumbers PreviousRow\r\n ON ThisRow.rank = PreviousRow.rank + 1\r\n LEFT OUTER JOIN @ranks ThisRowsRange\r\n ON ThisRow.number = ThisRowsRange.theMin\r\n LEFT OUTER JOIN @ranks PreviousRowsRange\r\n ON PreviousRow.number = PreviousRowsRange.theMin\r\n ORDER BY ThisRow.rank;\r\nRETURN STUFF(@Outputlist, 1, 1, '');\r\nEnd<\/pre>\nConverting a list with ranges to \u00a0a simple list to in T-SQL (SQL Server)<\/h2>\n
IF EXISTS\r\n (\r\n SELECT *\r\n FROM INFORMATION_SCHEMA.ROUTINES\r\n WHERE ROUTINES.ROUTINE_NAME = 'IntegerListfromRangesFunction'\r\n AND ROUTINES.ROUTINE_TYPE = 'function'\r\n )\r\nDROP FUNCTION dbo.IntegerListfromRangesFunction;\r\nGO\r\nCREATE FUNCTION IntegerListfromRangesFunction\r\n (\r\n \/**\r\nsummary: >\r\n This Procedure takes a list of integers with ranges in them and turns it into\r\n a list of integers. Effectively, it removes the ranges by turning them into all\r\n the integers in the range. This uses the iterative solution without an intermediary\r\n table\r\nAuthor: Phil Factor\r\nRevision: 1.0\r\ndate: 20 Nov 2016\r\nexample: >\r\n-eg. SELECT dbo.IntegerListfromRangesFunction('1-4,6,8-11,13,21-23,25,27-28,30')\r\n\t --which would become 1,2,3,4,6,8,9,10,11,13,21,22,23,25,27,28,30\r\nreturns: >\r\n The list as an ascll string representing a list of integers.\r\n**\/\r\n @list VARCHAR(MAX) --eg. 1-4,6,8-11,13,21-23,25,27-28,30\r\n )\r\nRETURNS VARCHAR(MAX)\r\nAS\r\n BEGIN\r\n DECLARE @c CHAR, --the current character being examined\r\n @ii INT, --the iteration count\r\n @len INT, --length of the list\r\n @negative BIT, --is this a negative number,\r\n @Accumulator INT, --the value of the number so far\r\n @ThereIsAnRvalue INT, --Is there an Rvalue in the accumulator\r\n @previous CHAR, --the previous character examined\r\n @lvalue INT, --left hand side value of ecxpression, \r\n @OutputList VARCHAR(MAX); -- the output list\r\n --so initialise everything\r\n SELECT @list = @list + ',', @ii = 1, @len = LEN(@list), @negative = 0,\r\n @Accumulator = 0, @previous = ',', @lvalue = 0, @OutputList = '',\r\n @ThereIsAnRvalue = 0;\r\n WHILE(@ii <= @len) --from the start to end of the list \r\n BEGIN\r\n SELECT @c = SUBSTRING(@list, @ii, 1); --get the next character\r\n IF @c = '-' AND @previous LIKE '[0-9]' --is it the range keyword\r\n SELECT @lvalue = --if it is, then save the lvalue\r\n CASE WHEN @negative = 1 THEN -@Accumulator ELSE @Accumulator END,\r\n @ThereIsAnRvalue = 1, @Accumulator = 0, @negative = 0;\r\n ELSE IF @c = '-' --it IS a negative number following\r\n SELECT @negative = 1;\r\n ELSE IF @c LIKE '[0-9]' --it is a number\r\n SELECT @Accumulator = (@Accumulator * 10) + @c;\r\n ELSE IF(@c = ',') --it is the end of the current list entry \r\n BEGIN\r\n SELECT @Accumulator =\r\n CASE WHEN @negative = 1 THEN -@Accumulator ELSE @Accumulator END;\r\n IF @ThereIsAnRvalue = 1\r\n WHILE(@lvalue <= @Accumulator)\r\n BEGIN\r\n SELECT @OutputList =\r\n @OutputList + ',' + CONVERT(VARCHAR(5), @lvalue);\r\n SELECT @lvalue = @lvalue + 1;\r\n END;\r\n ELSE\r\n SELECT @OutputList =\r\n @OutputList + ',' + CONVERT(VARCHAR(5), @Accumulator);\r\n SELECT @Accumulator = 0, @negative = 0, @lvalue = 0,\r\n @ThereIsAnRvalue = 0;\r\n END;\r\n SELECT @ii = @ii + 1, @previous = @c;\r\n END;\r\n RETURN STUFF(@OutputList, 1, 1, '');\r\n END;<\/pre>\n\/****** Object: StoredProcedure [dbo].[IntegerListfromRanges] Script Date: 01\/12\/2016 10:45:21 ******\/\r\n\r\nIF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES\r\n\tWHERE ROUTINES.ROUTINE_TYPE='procedure' \r\n\tAND ROUTINES.ROUTINE_SCHEMA='dbo'\r\n\tAND\tROUTINES.ROUTINE_NAME = 'IntegerListfromRanges')\r\n\tDROP PROCEDURE IntegerListfromRanges\r\ngo\r\nCREATE procedure [dbo].[IntegerListfromRanges]\r\n\/**\r\nsummary: >\r\n This Procedure takes a list of integers with ranges in them and turns it into\r\n a list of integers. Effectively, it removes the ranges by turning them into all\r\n the integers in the range. This is a demonstration of a working method because\r\n I create a number table on the fly with negative integers (these make things harder)\r\n and normally you'd just want to have the table of numbers that you would then\r\n use for whatever relational task you were engaged with\r\nAuthor: Phil Factor\r\nRevision: 1.0\r\ndate: 19 Nov 2016\r\nexample: >\r\n DECLARE @list VARCHAR(MAX)\r\n EXECUTE dbo.IntegerListFromRanges '-12--3,4,6,7',@list OUTPUT\r\n SELECT @list\r\nreturns: >\r\n The list as an ascll string representing a list of integers.\r\n**\/ \r\n@Rangelist VARCHAR(MAX), --eg. 1-4,6,8-11,13,21-23,25,27-28,30\r\n\t--which would become 1,2,3,4,6,8,9,10,11,13,21,22,23,25,27,28,30\r\n@list VARCHAR(MAX) output\r\nAS\r\n\r\nBEGIN \r\n--declare some variables\r\nDECLARE @inClause VARCHAR(MAX), @BetweenClause VARCHAR(MAX),\r\n @XMLVersionOfList XML,@NumberTableSQL NVARCHAR(MAX)\r\nDECLARE @MyNumbers TABLE\r\n (\r\n MyRange VARCHAR(10)\r\n);\r\n--here we create the numbers table with a precautionary test\r\nIF NOT EXISTS(\r\n SELECT TABLES.TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES \r\n WHERE TABLES.TABLE_NAME LIKE '#NumbersForRanges%')\r\n\tBEGIN\r\n SELECT TOP 10000 IDENTITY(int,-5000,1) AS Number\r\n INTO #NumbersForRanges\r\n FROM sys.objects s1 \r\n CROSS JOIN sys.objects s2; \r\n ALTER TABLE #NumbersForRanges ADD CONSTRAINT \r\n\t\tPK_NumbersForRanges PRIMARY KEY CLUSTERED (Number);\r\n\tend\r\n--we convert the list into XML\r\nSELECT @XMLVersionOfList =\r\n '<list><i>' + REPLACE(@rangelist, ',', '<\/i><i>') + '<\/i><\/list>';\r\n--we now take each component and insert it into a row of a table variable\r\nINSERT INTO @MyNumbers(MyRange)\r\n SELECT x.y.value('.', 'varchar(10)') AS IDs\r\n FROM @XMLVersionOfList.nodes('\/list\/i\/text()') AS x(y);\r\n-- end of section converting list to a table we can work with\r\nSELECT @inClause='', @BetweenClause=''\r\n--we want to make a SELECT statement against a number table\r\nSELECT \r\n @inClause=@inClause +CASE WHEN MyRange like '%[0-9]-%' THEN '' ELSE ',' + MyRange END,\r\n @BetweenClause=@BetweenClause \r\n +CASE WHEN MyRange like '%[0-9]-%' \r\n THEN 'or (number between '+STUFF(MyRange,PATINDEX('%[0-9]-%',[@MyNumbers].MyRange)+1,1,' and ')+') ' \r\n ELSE'' END\r\n FROM @MyNumbers\r\n SELECT @list=''\r\nIF @@RowCount>0 \r\n BEGIN\r\n SELECT @NumberTableSQL='SELECT @OutputList='''';\r\n SELECT @OutputList=@OutputList+ '',''+ convert(varchar(5),number) from #NumbersForRanges where ' +\r\n CASE WHEN LEN(@inClause)>0 THEN 'number in ('+ STUFF(@inClause,1,1,0)+') 'ELSE '' END+\r\n CASE WHEN LEN(@BetweenClause)>0 THEN CASE WHEN LEN(@inClause)>0 THEN 'or' ELSE '' END+\r\n\t' ('+ STUFF(@BetweenClause,1,3,'')+')'ELSE '' END;\r\n---now we execute the code against the number table\r\n EXEC sp_ExecuteSQL @NumberTableSQL, N'@OutputList Varchar(max) OUTPUT',@OutputList=@List OUTPUT\r\n SELECT @list=STUFF(@list,1,1,'')\r\n END\r\nend\r\ngo\r\n<\/pre>\nTesting<\/h1>\n
(-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<\/pre>\n
IF ( --there were any cases where the two conversions to and from ranges didnt match the original\r\n SELECT \r\n SUM(\r\n CASE WHEN list<> dbo.IntegerListfromRangesFunction( dbo.ConvertRangesInIntegerList(list)) \r\n THEN 1 ELSE 0 END)\r\n FROM dbo.listsOfNumbers\r\n )>0\r\nRAISERROR(\r\n 'there were discrepencies in the ranges before and after conversion',\r\n 16,\r\n 1\r\n);<\/pre>\n
SET NOCOUNT ON;\r\n--delete the temp table if it exists\r\nIF EXISTS (SELECT TABLES.TABLE_NAME\r\n FROM tempdb.INFORMATION_SCHEMA.TABLES\r\n WHERE TABLES.TABLE_NAME LIKE '#templist%'\r\n)\r\nDROP TABLE #templist;\r\n--create the table from the test ranges\r\nSELECT IDENTITY(INT, 1, 1) AS range_id, list AS list,\r\n dbo.ConvertRangesInIntegerList(list) AS ranged,\r\n CONVERT(VARCHAR(2000), '') AS after --we will insert into this \r\nINTO #tempList\r\n FROM dbo.listsOfNumbers;\r\n--make iterative stuff faster\r\nALTER TABLE #tempList\r\nADD CONSTRAINT PK_Identity PRIMARY KEY CLUSTERED(range_id);\r\n--now RBAR through, reversing the conversion and putting it in the third column\r\nDECLARE @ii INT, @iiMax INT, @Ranged VARCHAR(8000), @UnRanged VARCHAR(8000);\r\nSELECT @ii = 1, @iiMax = COUNT(*) FROM #tempList;\r\nWHILE @ii <= @iiMax\r\n BEGIN\r\n SELECT @Ranged = #tempList.ranged\r\n FROM #tempList\r\n WHERE #tempList.range_id = @ii;\r\n EXECUTE dbo.IntegerListfromRanges @Ranged, @UnRanged OUTPUT;\r\n UPDATE #tempList\r\n SET #tempList.after = @UnRanged\r\n WHERE #tempList.range_id = @ii;\r\n SELECT @ii = @ii + 1;\r\n END;\r\n--now check the two \r\nIF EXISTS (SELECT * FROM #tempList WHERE #tempList.list <> #tempList.after)\r\nRAISERROR(\r\n 'there were discrepencies in the ranges before and after conversion',\r\n 16,\r\n 1\r\n);\r\nSET NOCOUNT OFF;<\/pre>\n
Conclusion<\/h1>\n
References<\/h1>\n
\n