Post date: May 24, 2014 12:58:5 AM
You can add lists together. I don't know how to go about overriding "+" in SQL Server, so I made an addition method. I can't use the most obvious name, because Add is reserved and always comes up as a syntax error.
>>> DECLARE @a List, @b List
>>> SET @a = '[1, 2, 3]'
>>> SET @b = '[4, 5, 6]'
>>> PRINT CONVERT(nvarchar(max),@a.AddList(@b))
["1", "2", "3", "4", "5", "6"]
It's even more reserved than Count, which I can use for my own desires.
>>> PRINT @a.AddList(@b).Count
6
The AddList function adds the two lists to return the combination of the two lists, but doesn't modify either list. Now let's do it a different way, using the Extend method to modify a list, adding multiple items at once.
>>> DECLARE @c List = @a
>>> SET @c.Extend(@b)
>>> PRINT CONVERT(nvarchar(max),@c)
["1", "2", "3", "4", "5", "6"]
Lists can be multiplied, returning a repetition of all the values for each multiplier.
>>> PRINT CONVERT(nvarchar(max),@a.Multiply(3))
["1", "2", "3", "1", "2", "3", "1", "2", "3"]
You can append a single item to a list. This is often how lists are built, starting with a blank list [] and then adding items one by one.
>>> SET @c.Append('7')
>>> PRINT CONVERT(nvarchar(max),@c)
["1", "2", "3", "4", "5", "6", "7"]
Which brings us to NULL. I haven't coded a way to include NULL vs the string NULL if you want to do SET @l = [NULL], but you can add NULL to a list by appending it by itself.
>>> SET @c.Append(NULL)
>>> PRINT CONVERT(nvarchar(max),@c)
["1", "2", "3", "4", "5", "6", "7", <NULL>]
You can replace some items in a list with a different list, which can either be the same length or a different length. To understand how the start index and end index are used to determine what records are replaced, use the slicing logic described in my previous post.
>>> SET @c.ReplaceItems(1, 3, CONVERT(List, '[ a, b, c]'))
>>> PRINT CONVERT(nvarchar(max),@c)
["1", "a", "b", "c", "4", "5", "6", "7", <NULL>]
Remove some items by replacing them with NULL. Here, I am removing the last item in the list (index -1).
>>> SET @c.ReplaceItems(-1, NULL, NULL);
>>> PRINT CONVERT(nvarchar(max),@c)
["1", "a", "b", "c", "4", "5", "6", "7"]
This syntax does the same thing.
>>> SET @c.RemoveItems(-1, NULL);
>>> PRINT CONVERT(nvarchar(max),@c)
["1", "a", "b", "c", "4", "5", "6"]
Insert a list of items into the middle of the list.
>>> SET @c.ReplaceItems(2, 2, CONVERT(List, '[spam, eggs]'))
>>> PRINT CONVERT(nvarchar(max),@c)
["1", "a", "spam", "eggs", "b", "c", "4", "5", "6"]
This syntax does the same thing.
>>> SET @c.InsertItems(2, CONVERT(list, 'x, y'))
>>> PRINT CONVERT(nvarchar(max),@c)
["1", "a", "x", "y", "spam", "eggs", "b", "c", "4", "5", "6"]
Insert a single item.
>>> SET @c.InsertItem(4, 'z')
>>> PRINT CONVERT(nvarchar(max),@c)
["1", "a", "x", "y", "z", "spam", "eggs", "b", "c", "4", "5", "6"]
Here's something a little weird that Python lets you do, so I implemented it too. You can slice the list with an interval, returning a list that skips every other item.
>>> SET @a = '[a, b, c, d, e, f, g, h, i]'
>>> PRINT CONVERT(nvarchar(max),@a.GetItemsWithInterval(0, NULL, 2))
["a", "c", "e", "g", "i"]
Or every third item, from the second to the 8th.
>>> PRINT CONVERT(nvarchar(max),@a.GetItemsWithInterval(1, 8, 3))
["b", "e", "h"]
You can even use a negative step to get a (hard-to-use) reversed stepping. This is something that really doesn't make sense even in Python, and was a major pain to re-implement. I did it because it was challenging.
>>> SET @a = '0, 1, 2, 3, 4, 5, 6, 7'
>>> PRINT CONVERT(nvarchar(max),@a.GetItemsWithInterval(5, 1, -2))
["5", "3"]
But if you want to reverse a list, don't use the terrible negative interval method. That's mainly a way to make your code unmaintainable. Use the methods designed for the purpose. Reversed returns a reversed copy of the original.
>>> SET @a = '[the, ministry, of, silly, walks]'
>>> PRINT CONVERT(nvarchar(max), @a) + ', ' + CONVERT(nvarchar(max), @a.Reversed())
["the", "ministry", "of", "silly", "walks"], ["walks", "silly", "of", "ministry", "the"]
You can also return a sorted (alphabetical) copy.
>>> PRINT CONVERT(nvarchar(max), @a) + ', ' + CONVERT(nvarchar(max), @a.Sorted())
["the", "ministry", "of", "silly", "walks"], ["ministry", "of", "silly", "the", "walks"]
Or sort and reverse the lists in place, modifying the original.
>>> SET @a.Sort()
>>> PRINT CONVERT(nvarchar(max),@a)
["ministry", "of", "silly", "the", "walks"]
>>> SET @a.Reverse()
>>> PRINT CONVERT(nvarchar(max),@a)
["walks", "the", "silly", "of", "ministry"]
I'm now almost done with everything I felt like adding. I guess I'll finish. You can search a list for an item by using ContainsValue.
>>> SET @a = '[this, method, does, not, use, the, best, name, because, "contains", is reserved, by, SQL Server]'
>>> PRINT @a.ContainsValue('contains')
1
ContainsValue is case-sensitive but there is another option.
>>> PRINT @a.ContainsValue('CONTAINS')
0
>>> PRINT @a.ContainsValueCaseInsensitive('CONTAINS')
1
You can search for several items (performance is probably not good for large lists).
>>> SET @b = '[the, ministry, of, silly, walks]'
>>> PRINT @a.ContainsAnyValue(@b)
1
You can find the indexes of text values. It returns -1 if there is no match.
>>> PRINT @b.FindIndex('silly')
3
You can get all the matching indexes in a list. I am hoping this performs better than going through the list one by one and checking the value. Of course, if I wanted to do this right, I'd have to make an IndexList user-defined type that would store a list of integers, but for now, it returns the same List datatype.
>>> SET @a = '[spam, eggs, spam, spam, bacon, and, spam]'
>>> SET @b = @a.FindAllIndex('spam')
>>> PRINT CONVERT(nvarchar(max),@b)
["0", "2", "3", "6"]
Here's the last little bit of code. The most warped piece of the list data type: looping. You can loop over a SQL list, but it takes some steps, because we can't do a proper enumeration in SQL. We can't do a for-each loop. But we can fake one. You have to code it in a specific way that is a little annoying, but is designed to be as simple as possible, considering the main SQLCLR constraints. I think this pattern is easy enough to use. It is the same pattern I use when using cursors.
>>> SET @a = '[knights, what, say, ni]'
>>> WHILE 1 = 1
>>> BEGIN
>>> SET @a.Next()
>>> IF @a.StopIteration = 1 BREAK
>>> /* Custom code goes here */
>>> PRINT RTRIM(@a.CurrentIndex) + ', ' + @a.CurrentItem
>>> /* End custom code */
>>> END
0, knights
1, what
2, say
3, ni
If you try to get the current item after you've hit the end of the list, there will be an ugly error.
>>> PRINT @a.CurrentItem
Msg 6522, Level 16, State 1, Line 196
A .NET Framework error occurred during execution of user-defined routine or aggregate "List":
System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values.
Parameter name: Current index has passed the end of the list.
System.ArgumentOutOfRangeException:
at List.get_CurrentItem()
.
That is why I check for StopIteration being true before I attempt to read from the list. If it were an empty list, it saves a blowup.
If you reach the end of the list, you can reset the fake iterator and start over.
>>> SET @a.ResetIterator()
>>> SET @a.Next()
>>> PRINT @a.CurrentItem
knights
Note that I had to start by fetching the first value, and then read the item in the list. If I tried to use CurrentItem before I loaded the first item, I would see another ArgumentOutOfRangeException. The main reason for this syntax was that in CLR user-defined types, you cannot modify the variable and get back data at the same time. So you can't have Next() return the value. You also can't implement the useful Pop() command. You must always get the data and delete the item in separate steps. When you compare the code needed for CurrentItem before Next() and Next() before CurrentItem, next-first iteration produces MUCH cleaner code.