sql server 中将由逗号“，”分割的一个字符串，转换为一个表，并应用与 in 条件

可以把形如“1,2,3”这样的字符串转换为一个临时表，这个表有一列，3行，每一行存一个项目（用逗号分隔开的一部分）

该函数可以这样写:

<span style="color: #0000ff;">GO</span>
<span style="color: #008080;">/*</span><span style="color: #008080;">***** Object:  UserDefinedFunction [dbo].[StrToTable]    Script Date: 2016/3/1 18:53:35 *****</span><span style="color: #008080;">*/</span>
<span style="color: #0000ff;">SET</span> ANSI_NULLS <span style="color: #0000ff;">ON</span>
<span style="color: #0000ff;">GO</span>
<span style="color: #0000ff;">SET</span> QUOTED_IDENTIFIER <span style="color: #0000ff;">ON</span>
<span style="color: #0000ff;">GO</span>
<span style="color: #0000ff;">ALTER</span> <span style="color: #0000ff;">Function</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">StrToTable</span><span style="color: #ff0000;">]</span>(<span style="color: #008000;">@str</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">1000</span><span style="color: #000000;">))
</span><span style="color: #0000ff;">Returns</span> <span style="color: #008000;">@tableName</span> <span style="color: #0000ff;">Table</span><span style="color: #000000;">
(
   ID </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">50</span><span style="color: #000000;">)
)
</span><span style="color: #0000ff;">As</span>
<span style="color: #008080;">--</span><span style="color: #008080;">该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列，例如字符串‘1,2,3,4,5‘ 将编程一个表，这个表</span>
<span style="color: #0000ff;">Begin</span>
<span style="color: #0000ff;">set</span> <span style="color: #008000;">@str</span> <span style="color: #808080;">=</span> <span style="color: #008000;">@str</span><span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span>
<span style="color: #0000ff;">Declare</span> <span style="color: #008000;">@insertStr</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">50</span>) <span style="color: #008080;">--</span><span style="color: #008080;">截取后的第一个字符串</span>
<span style="color: #0000ff;">Declare</span> <span style="color: #008000;">@newstr</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">1000</span>) <span style="color: #008080;">--</span><span style="color: #008080;">截取第一个字符串后剩余的字符串</span>
<span style="color: #0000ff;">set</span> <span style="color: #008000;">@insertStr</span> <span style="color: #808080;">=</span> <span style="color: #808080;">left</span>(<span style="color: #008000;">@str</span>,<span style="color: #ff00ff;">charindex</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span>,<span style="color: #008000;">@str</span>)<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">set</span> <span style="color: #008000;">@newstr</span> <span style="color: #808080;">=</span> <span style="color: #ff00ff;">stuff</span>(<span style="color: #008000;">@str</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff00ff;">charindex</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span>,<span style="color: #008000;">@str</span>),<span style="color: #ff0000;">‘‘</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">Insert</span> <span style="color: #008000;">@tableName</span> <span style="color: #0000ff;">Values</span>(<span style="color: #008000;">@insertStr</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">while</span>(<span style="color: #ff00ff;">len</span>(<span style="color: #008000;">@newstr</span>)<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">begin</span>
   <span style="color: #0000ff;">set</span> <span style="color: #008000;">@insertStr</span> <span style="color: #808080;">=</span> <span style="color: #808080;">left</span>(<span style="color: #008000;">@newstr</span>,<span style="color: #ff00ff;">charindex</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span>,<span style="color: #008000;">@newstr</span>)<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">)
   </span><span style="color: #0000ff;">Insert</span> <span style="color: #008000;">@tableName</span> <span style="color: #0000ff;">Values</span>(<span style="color: #008000;">@insertStr</span><span style="color: #000000;">)
   </span><span style="color: #0000ff;">set</span> <span style="color: #008000;">@newstr</span> <span style="color: #808080;">=</span> <span style="color: #ff00ff;">stuff</span>(<span style="color: #008000;">@newstr</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff00ff;">charindex</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span>,<span style="color: #008000;">@newstr</span>),<span style="color: #ff0000;">‘‘</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">end</span>
<span style="color: #0000ff;">Return</span>
<span style="color: #0000ff;">END</span>

然后sql语句就可以这样了

<span style="color: #0000ff;">declare</span> <span style="color: #ff00ff;">str</span> vchar(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">set</span> <span style="color: #ff00ff;">str</span><span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1,2,3</span><span style="color: #ff0000;">‘</span>
<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> tablename <span style="color: #0000ff;">where</span> id <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> ID <span style="color: #0000ff;">from</span> StrToTable(<span style="color: #008000;">@str</span>) )

sql server 中将由逗号“，”分割的一个字符串，转换为一个表，并应用与 in 条件

标签：