Post date: Apr 23, 2014 1:12:25 AM
Here is something that I'm working on occasionally, when I feel interested. It's not very practical, or efficient, and you should never consider storing something like this in a table, but it's neat and one day, there may be a problem that it solves. It is a list datatype for SQL Server. When I feel it is reasonably finished, I'll provide the SQL CLR code to make it happen. For now, this is one half preview and one half tutorial.
These lists are a lot simpler than lists in Python. They are always lists of strings, never of integers, datetimes, or objects. They need to be a known datatype because this is SQL and C#, where everything has a known datatype (I'm ignoring for now the new dynamic keyword in .NET 4.0, which is a neat idea, but not helpful in T-SQL). I chose strings selected because almost everything has a text representation, as anyone who has written code knows. Even user-defined CLR datatypes have string representations, which are used in the hidden Parse and ToString methods, as I will demonstrate presently.
>>> DECLARE @a List
>>> SET @a = '["spam", ''eggs'', 1234, """5678""", ''''''ouch!'''''']' -- Parse
>>> PRINT CONVERT(nvarchar(max),@a) -- ToString
["spam", "eggs", "1234", "5678", "ouch!"]
A list is a collection of string values, separated by commas, and typically surrounded by quotation marks. But because each list item is known to be a string, the quotes are optional. If you want a list entry containing a comma, though, you will need to quote your string. Also, list items are trimmed, so that "spam, eggs, not spam" doesn't become "spam" and " eggs," so if you want padding space, you also need to quote your strings.
You have your choice of quote types, either ' (single), " (double), """ (triple double), or ''' (triple single), as long as the beginning and ending quotes for a list item are the same. SQL being what it is, you'll probably prefer double quotes. It saves on doubling up every single quote in the string. The reason for four types of quotes is to allow you to include quotes in your list items, without spending the time to write code for escaping special characters. I'm not up for writing that right now.
>>> SET @a = '[''a "mixed," comma-containing list'', is, "still", " ''legal'' "]'
>>> PRINT CONVERT(nvarchar(max),@a)
['a "mixed," comma-containing list', "is", "still", " 'legal' "]
If you start with a triple quote, you must end with a triple quote. Don't start with """ and end with ", hoping that the quotes will match. They won't.
>>> SET @a = '[this, is, """a "very" ''special'' after-school special"""]'
>>> PRINT CONVERT(nvarchar(max),@a)
["this", "is", """a "very" 'special' after-school special"""]
Now we'll end with three secrets about the parsing logic.
Parsing secret #1: Only quotes next to a comma or the end of the list count, so this example, with embedded quotes of the same type as the outer quotes, is legal:
>>> SET @a = '["this is "legal" usage, ''but I recommend against it'']'
>>> PRINT CONVERT(nvarchar(max),@a)
[""""this is "legal" usage, 'but I recommend against it'"""]
Parsing secret #2: [ ] are optional. We don't need them to know that @a is a List. We know @a is a List because that is how it was declared. [ ] are included only for readability.
>>> SET @a = 'silly, walks'
>>> PRINT CONVERT(nvarchar(max),@a)
["silly", "walks"]
Parsing secret #3: This unclosed string at the end of this should blow up, and would blow up in Python, but I decided to be nice and allow it:
>>> SET @a = '[vikings, "parrots", "Dinsdale]'
>>> PRINT CONVERT(nvarchar(max),@a)
["vikings", "parrots", '"Dinsdale']
Ciao for today.