实现SQL Server中的切割字符串SplitString标量函数代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】实现SQL Server中的切割字符串SplitString标量函数
作者:Dezai.CN / 发布于2011/11/15/ 787
<div><span style="color: rgb(0,0,255)">SET</span> ANSI_NULLS <span style="color: rgb(0,0,255)">ON</span>
<span style="color: rgb(0,0,255)">GO</span>
<span style="color: rgb(0,0,255)">SET</span> QUOTED_IDENTIFIER <span style="color: rgb(0,0,255)">ON</span>
<span style="color: rgb(0,0,255)">GO</span>


<span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">
by kudychen 2011-9-28 
</span><span style="color: rgb(0,128,128)">*/</span>
<span style="color: rgb(0,0,255)">CREATE</span> <span style="color: rgb(0,0,255)">function</span> <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">dbo</span><span style="color: rgb(255,0,0)">]</span>.<span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">SplitString</span><span style="color: rgb(255,0,0)">]</span>
(
 <span style="color: rgb(0,128,0)">@Input</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(255,0,255)">max</span>), <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">input string to be separated</span><span style="color: rgb(0,128,128)">
</span> <span style="color: rgb(0,128,0)">@Separator</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(255,0,255)">max</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">,</span><span style="color: rgb(255,0,0)">'</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">a string that delimit the substrings in the input string</span><span style="color: rgb(0,128,128)">
</span> <span style="color: rgb(0,128,0)">@RemoveEmptyEntries</span> <span style="color: rgb(0,0,255)">bit</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">the return value does not include array elements that contain an empty string</span><span style="color: rgb(0,128,128)">
</span>)
<span style="color: rgb(0,0,255)">returns</span> <span style="color: rgb(0,128,0)">@TABLE</span> <span style="color: rgb(0,0,255)">table</span> 
(
 <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">Id</span><span style="color: rgb(255,0,0)">]</span> <span style="color: rgb(0,0,255)">int</span> <span style="color: rgb(255,0,255)">identity</span>(<span style="color: rgb(128,0,0); font-weight: bold">1</span>,<span style="color: rgb(128,0,0); font-weight: bold">1</span>),
 <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">Value</span><span style="color: rgb(255,0,0)">]</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(255,0,255)">max</span>)
) 
<span style="color: rgb(0,0,255)">as</span>
<span style="color: rgb(0,0,255)">begin</span> 
 <span style="color: rgb(0,0,255)">declare</span> <span style="color: rgb(0,128,0)">@Index</span> <span style="color: rgb(0,0,255)">int</span>, <span style="color: rgb(0,128,0)">@Entry</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(255,0,255)">max</span>)
 <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@Index</span> <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,255)">charindex</span>(<span style="color: rgb(0,128,0)">@Separator</span>,<span style="color: rgb(0,128,0)">@Input</span>)

 <span style="color: rgb(0,0,255)">while</span> (<span style="color: rgb(0,128,0)">@Index</span><span style="color: rgb(128,128,128)">></span><span style="color: rgb(128,0,0); font-weight: bold">0</span>)
 <span style="color: rgb(0,0,255)">begin</span>
 <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@Entry</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">ltrim</span>(<span style="color: rgb(255,0,255)">rtrim</span>(<span style="color: rgb(255,0,255)">substring</span>(<span style="color: rgb(0,128,0)">@Input</span>, <span style="color: rgb(128,0,0); font-weight: bold">1</span>, <span style="color: rgb(0,128,0)">@Index</span><span style="color: rgb(128,128,128)">-</span><span style="color: rgb(128,0,0); font-weight: bold">1</span>)))
 
 <span style="color: rgb(0,0,255)">if</span> (<span style="color: rgb(0,128,0)">@RemoveEmptyEntries</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">0</span>) <span style="color: rgb(128,128,128)">or</span> (<span style="color: rgb(0,128,0)">@RemoveEmptyEntries</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(128,128,128)">and</span> <span style="color: rgb(0,128,0)">@Entry</span><span style="color: rgb(128,128,128)"><></span><span style="color: rgb(255,0,0)">''</span>)
 <span style="color: rgb(0,0,255)">begin</span>
 <span style="color: rgb(0,0,255)">insert</span> <span style="color: rgb(0,0,255)">into</span> <span style="color: rgb(0,128,0)">@TABLE</span>(<span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">Value</span><span style="color: rgb(255,0,0)">]</span>) <span style="color: rgb(0,0,255)">Values</span>(<span style="color: rgb(0,128,0)">@Entry</span>)
 <span style="color: rgb(0,0,255)">end</span>

 <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@Input</span> <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,255)">substring</span>(<span style="color: rgb(0,128,0)">@Input</span>, <span style="color: rgb(0,128,0)">@Index</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">datalength</span>(<span style="color: rgb(0,128,0)">@Separator</span>)<span style="color: rgb(128,128,128)">/</span><span style="color: rgb(128,0,0); font-weight: bold">2</span>, <span style="color: rgb(255,0,255)">len</span>(<span style="color: rgb(0,128,0)">@Input</span>))
 <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@Index</span> <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,255)">charindex</span>(<span style="color: rgb(0,128,0)">@Separator</span>, <span style="color: rgb(0,128,0)">@Input</span>)
 <span style="color: rgb(0,0,255)">end</span>
 
 <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@Entry</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">ltrim</span>(<span style="color: rgb(255,0,255)">rtrim</span>(<span style="color: rgb(0,128,0)">@Input</span>))
 <span style="color: rgb(0,0,255)">if</span> (<span style="color: rgb(0,128,0)">@RemoveEmptyEntries</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">0</span>) <span style="color: rgb(128,128,128)">or</span> (<span style="color: rgb(0,128,0)">@RemoveEmptyEntries</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(128,128,128)">and</span> <span style="color: rgb(0,128,0)">@Entry</span><span style="color: rgb(128,128,128)"><></span><span style="color: rgb(255,0,0)">''</span>)
 <span style="color: rgb(0,0,255)">begin</span>
 <span style="color: rgb(0,0,255)">insert</span> <span style="color: rgb(0,0,255)">into</span> <span style="color: rgb(0,128,0)">@TABLE</span>(<span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">Value</span><span style="color: rgb(255,0,0)">]</span>) <span style="color: rgb(0,0,255)">Values</span>(<span style="color: rgb(0,128,0)">@Entry</span>)
 <span style="color: rgb(0,0,255)">end</span>

 <span style="color: rgb(0,0,255)">return</span>
<span style="color: rgb(0,0,255)">end</span>

如何使用：
<div class="cnblogs_code">
<pre><span style="color: rgb(0,0,255)">declare</span> <span style="color: rgb(0,128,0)">@str1</span> <span style="color: rgb(0,0,255)">varchar</span>(<span style="color: rgb(255,0,255)">max</span>), <span style="color: rgb(0,128,0)">@str2</span> <span style="color: rgb(0,0,255)">varchar</span>(<span style="color: rgb(255,0,255)">max</span>), <span style="color: rgb(0,128,0)">@str3</span> <span style="color: rgb(0,0,255)">varchar</span>(<span style="color: rgb(255,0,255)">max</span>)<span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@str1</span> <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">1,2,3</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@str2</span> <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">1###2###3</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@str3</span> <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">1###2###3###</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,255)">select</span> <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">Value</span><span style="color: rgb(255,0,0)">]</span> <span style="color: rgb(0,0,255)">from</span> <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">dbo</span><span style="color: rgb(255,0,0)">]</span>.<span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">SplitString</span><span style="color: rgb(255,0,0)">]</span>(<span style="color: rgb(0,128,0)">@str1</span>, <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">,</span><span style="color: rgb(255,0,0)">'</span>, <span style="color: rgb(128,0,0); font-weight: bold">1</span>)<span style="color: rgb(0,0,255)">select</span> <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">Value</span><span style="color: rgb(255,0,0)">]</span> <span style="color: rgb(0,0,255)">from</span> <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">dbo</span><span style="color: rgb(255,0,0)">]</span>.<span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">SplitString</span><span style="color: rgb(255,0,0)">]</span>(<span style="color: rgb(0,128,0)">@str2</span>, <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">###</span><span style="color: rgb(255,0,0)">'</span>, <span style="color: rgb(128,0,0); font-weight: bold">1</span>)<span style="color: rgb(0,0,255)">select</span> <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">Value</span><span style="color: rgb(255,0,0)">]</span> <span style="color: rgb(0,0,255)">from</span> <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">dbo</span><span style="color: rgb(255,0,0)">]</span>.<span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">SplitString</span><span style="color: rgb(255,0,0)">]</span>(<span style="color: rgb(0,128,0)">@str3</span>, <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">###</span><span style="color: rgb(255,0,0)">'</span>, <span style="color: rgb(128,0,0); font-weight: bold">0</span>)</pre>
</div>
</div>
试试其它关键字
　切割字符串　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)