sql server 中的数组和列表（转载）

转载自：Arrays and Lists in SQL Server-The Short Version

原文摘录

Two Simple Multi-Statement Functions
If you search the web, there is no end of functions to split strings into table format. Here, I will present two simple functions that run on SQL 2008 or later, one for a list of integers and one for a list of strings. I should immediately warn you that these functions are not the most efficient and therefore not suitable if you have long lists with thousands of elements. But they are perfectly adequate if you are passing the contents of a multi-choice checkbox from a client where you would rarely have as many as 50 elements.

I opted to share these functions because they are simple and you can easily adapt them if you want different behaviour with regards to the choices that I have made. In my long article, I describe methods that are faster, but they all require extra setup than just a function.

Below is a function to split a delimited list of integers. The function accepts a parameter for the delimiter which can be up to 10 characters long. The function returns the list positions for the elements. An empty element is returned as NULL. If there is a non-numeric value in the list, there will be a conversion error.

sql

CREATE FUNCTION intlist_to_tbl (@list  nvarchar(MAX),
                                @delim nvarchar(10))
   RETURNS @tbl TABLE (listpos int NOT NULL IDENTITY(1,1),
                       n       int NULL) AS
BEGIN
   DECLARE @pos       int = 1,
           @nextpos   int = 1,
           @valuelen  int,
           @delimlen  int = datalength(@delim) / 2

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(@delim COLLATE Czech_BIN2, @list, @pos)
      SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos
      INSERT @tbl (n)
         VALUES (convert(int, nullif(substring(@list, @pos, @valuelen), '')))
      SELECT @pos = @nextpos + @delimlen
   END
   RETURN
END

You are likely to be puzzled by the COLLATE clause. This is a small speed booster. By forcing a binary collation, we avoid that SQL Server employs the full Unicode rules when searching for the delimiter. This pays off when scanning long strings. Why Czech? The language does not matter here, so I just picked one with a short name.

And why datalength divided by 2 and not len? datalength returns the length in bytes, whence the division. len does not count trailing spaces, so it does not work if the delimiter is a space.

Here are two examples:

sql

SELECT * FROM intlist_to_tbl('1,2,3, 677,7 , ,-1', ',')
SELECT * FROM intlist_to_tbl('1<->2<->3<-> 677<->7<-><->-1', '<->')

Since the values are the same in both lists, the output is the same:

listpos	n
1	1
2	2
3	3
4	677
5	7
6	NULL
7	-1

Here is an example of how you would use it in a simple query:

sql

SELECT ...
FROM   tbl 
WHERE  col IN (SELECT n FROM intlist_to_tbl('1,2,3,4', ','))

If you find that you are only using comma-separated lists, you may grow tired of having to specify the delimiter every time. To that end, this wrapper can be handy:

sql

CREATE FUNCTION intlisttotbl (@list nvarchar(MAX)) RETURNS TABLE AS
RETURN (
   SELECT listpos, n FROM intlist_to_tbl(@list, ',')
)

I leave it as an exercise to the reader to come up with a better name.

Here is a function for a list of strings. It accepts an input parameter of the type nvarchar(MAX), but the return table has both a varchar and an nvarchar column. I will return to why in a second. Like intlist_to_tbl it returns the list position. It trims leading and trailing spaces. In difference to intlist_to_tbl, empty elements are returned as empty strings and not as NULL.

sql

CREATE FUNCTION strlist_to_tbl (@list  nvarchar(MAX),
                                @delim nvarchar(10))
   RETURNS @tbl TABLE (listpos int NOT NULL IDENTITY(1,1),
                       str     varchar(4000) NOT NULL,
                       nstr    nvarchar(4000) NOT NULL) AS
BEGIN
   DECLARE @pos       int = 1,
           @nextpos   int = 1,
           @valuelen  int,
           @nstr      nvarchar(4000),
           @delimlen  int = datalength(@delim) / 2

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(@delim COLLATE Czech_BIN2, @list, @pos)
      SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos
      SELECT @nstr = ltrim(rtrim(substring(@list, @pos, @valuelen)))
      INSERT @tbl (str, nstr)
         VALUES (@nstr, @nstr)
      SELECT @pos = @nextpos + @delimlen
   END
   RETURN
END

Here are two examples:

sql

SELECT * FROM strlist_to_tbl(N'Alpha (α) | Beta (β)|Gamma (γ)|Delta (δ)|', '|')
SELECT * FROM strlist_to_tbl(N'a///b///c///v///x', '///')

Here is the output:

listpos | str | nstr
|:–:|:–:|:–:|
1 | Alpha (a)| Alpha (α)
2 | Beta (ß) | Beta (β)
3 | Gamma (?)| Gamma (γ)
4 | Delta (d)| Delta (δ)
5 | | |

listpos | str | nstr
|:–:|:–:|:–:|
1 | a | a
2 | b | b
3 | c | c
4 | v | v
5 | x | x

Note in the first result set that the Greek characters has been replaced by fallback characters in the str column. They are unchanged in the nstr column. (If you have a Greek or a UTF-8 collation, the two columns will be identical, though.)

Here are two examples of using this function:

sql

SELECT ...
FROM   tbl 
WHERE  varcharcol IN (SELECT str FROM strlist_to_tbl('a,b,c', ','))

SELECT ...
FROM   tbl 
WHERE  nvarcharcol IN (SELECT nstr FROM strlist_to_tbl('a,b,c', ','))

These examples illustrate why there are two columns. If you are going to use the list against a varchar column, you need to use the str column. This is important because of the type-conversion rules in SQL Server. If you mistakenly compare varcharcol to nstr, varcharcol will be converted to nvarchar, and this can render any index on varcharcol ineligible for the query, leading to a performance disaster as the table must be scanned. And conversely, if you have an nvarchar column, you need to compare it to the nvarchar value, since else the result can be incorrect because of the character replacement with the conversion to varchar.

I like to point out that these functions are by no means cast in stone, but see them as suggestions. Feel free to modify them according to your preferences and needs.