T-SQL有效地将5个逗号分隔的字符串解析为一个表

Question

我已经摸索了很多方法来拼凑这些，但需要关于最高效的方法来完成任务的建议。

我正在编写一个从前端传递5个变量的过程。每个变量都是一个varchar，最多包含3118个逗号分隔的元素。我需要把这些东西放在一张桌子上，然后以高效的方式返回。

我发现了解析例程、数据透视方法以及使用CTE、临时表和表变量的方法。

下面的例子只是一个例子——在现实生活中，这些字符串可能会很长。所以我希望避免冗长的实验，看看你们是否能为我指出应对挑战的最佳方法。

--*--从这个开始：

declare @decimal varchar(50), @binary varchar(50), @primes varchar(50), @hex varchar(50), var5 varchar(50)
set @decimal = '1,2,3,4,5'
set @binary = '1,2,4,8,16'
set @primes = '1,2,3,5,7,11'
set @hex = '1','16','256','4096'
set @ancient = 'one','two','many' 

--创建一个输出表，如下所示：

declare @results table (id int identity(1,1),  [integer] varchar(1), [binary] varchar(1), prime varchar(50), hex varchar(50), ancient varchar (50))
id  integer  binary  prime  hex    ancient
1   1        1       1      1      one
2   2        2       2      16     two
3   3        4       3      256    many
4   4        8       5      4096   null
5   5        16      7      null   null
6   null     null    11     null   null

--*

Answer 1

我会使用一个如下所示的XML参数。

<r>
  <i>1</i>
  <b>1</b>    
  <p>1</p>
  <h>1</h>
  <a>one</a>
</r>
<r>
  <i>2</i>
  <b>2</b>    
  <p>2</p>
  <h>16</h>
  <a>two</a>
</r>

并像这样插入到表变量中。

insert into @results([integer], [binary], prime, hex, ancient)
select T.N.value('i[1]', 'varchar(1)'),
       T.N.value('b[1]', 'varchar(1)'),
       T.N.value('p[1]', 'varchar(50)'),
       T.N.value('h[1]', 'varchar(50)'),
       T.N.value('a[1]', 'varchar(50)')
from @xml.nodes('/r') as T(N)

根据我的经验，这已经足够有效了。从SQL Server 2008中，您可以改用表值参数。我没有用过，所以我不能告诉你它是否表现得更好。

Answer 2

您可以考虑调用一个程序集来为您进行此解析。这里有一个教程，MSDN上的一些文档和另一个例子