📄 [sqlserver]数据库中自定义拆分字符串函数split() - _net人字拖 - 博客园.mht
字号:
PADDING-BOTTOM: 4px; BORDER-LEFT: #cccccc 1px solid; WIDTH: 98%; =
PADDING-TOP: 4px; BORDER-BOTTOM: #cccccc 1px solid; BACKGROUND-COLOR: =
#eeeeee"><!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
--><SPAN style=3D"COLOR: #008080">--</SPAN><SPAN=20
style=3D"COLOR: =
#008080"> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
</SPAN><SPAN=20
style=3D"COLOR: #008080"><BR>--</SPAN><SPAN=20
style=3D"COLOR: =
#008080"> Author: <=
;myxbing></SPAN><SPAN=20
style=3D"COLOR: #008080"><BR>-</SPAN><SPAN style=3D"COLOR: #008080">-=20
Create date: <2007/8/17></SPAN><SPAN=20
style=3D"COLOR: #008080"><BR>--</SPAN><SPAN=20
style=3D"COLOR: #008080"> Description: =20
<=E6=8B=86=E5=88=86=E5=AD=97=E7=AC=A6=E4=B8=B2=E5=87=BD=E6=95=B0></=
SPAN><SPAN style=3D"COLOR: #008080"><BR>--</SPAN><SPAN=20
style=3D"COLOR: =
#008080"> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
</SPAN><SPAN=20
style=3D"COLOR: #008080"><BR></SPAN><SPAN=20
style=3D"COLOR: #0000ff">CREATE</SPAN><SPAN style=3D"COLOR: #000000"> =
</SPAN><SPAN=20
style=3D"COLOR: #0000ff">FUNCTION</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN style=3D"COLOR: =
#ff0000">[</SPAN><SPAN=20
style=3D"COLOR: #ff0000">dbo</SPAN><SPAN style=3D"COLOR: =
#ff0000">]</SPAN><SPAN=20
style=3D"COLOR: #000000">.</SPAN><SPAN style=3D"COLOR: =
#ff0000">[</SPAN><SPAN=20
style=3D"COLOR: #ff0000">Split</SPAN><SPAN style=3D"COLOR: =
#ff0000">]</SPAN><SPAN=20
style=3D"COLOR: #000000"><BR>(<BR> </SPAN><SPAN=20
style=3D"COLOR: #008000">@SplitString</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #000000">text</SPAN><SPAN=20
style=3D"COLOR: #000000">, </SPAN><SPAN style=3D"FONT-SIZE: 10pt"><SPAN=20
style=3D"COLOR: #008080">-- =
=E5=A6=82=E6=9E=9C=E8=A6=81=E4=BC=A0=E5=85=A5NText=E7=B1=BB=E5=9E=8B=EF=BC=
=8C=E4=B8=8B=E9=9D=A2=E9=9C=80=E8=A6=81=E7=9B=B8=E5=BA=94=E7=9A=84=E4=BF=AE=
=E6=94=B9=EF=BC=8C=E6=B3=A8=E9=87=8A=E8=A1=8C=E4=B8=BANText=E4=B8=8B=E5=90=
=8C</SPAN></SPAN><SPAN=20
style=3D"COLOR: #000000"><BR> </SPAN><SPAN=20
style=3D"COLOR: #008000">@Separator</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #000000">varchar</SPAN><SPAN=20
style=3D"COLOR: #000000">(</SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #800000">2</SPAN><SPAN=20
style=3D"COLOR: #000000">) </SPAN><SPAN style=3D"COLOR: =
#808080">=3D</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN style=3D"COLOR: =
#ff0000">'</SPAN><SPAN=20
style=3D"COLOR: #ff0000">,</SPAN><SPAN style=3D"COLOR: =
#ff0000">'</SPAN><SPAN=20
style=3D"FONT-SIZE: 10pt"><SPAN style=3D"FONT-SIZE: 10pt"><SPAN=20
style=3D"COLOR: #008080">-- NVarChar(2) =3D =
N','</SPAN></SPAN></SPAN><SPAN=20
style=3D"COLOR: #000000"><BR>)<BR></SPAN><SPAN=20
style=3D"COLOR: #0000ff">RETURNS</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #008000">@SplitStringsTable</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #0000ff">TABLE</SPAN><SPAN=20
style=3D"COLOR: #000000"><BR>(<BR> </SPAN><SPAN=20
style=3D"COLOR: #ff0000">[</SPAN><SPAN style=3D"COLOR: =
#ff0000">id</SPAN><SPAN=20
style=3D"COLOR: #ff0000">]</SPAN><SPAN style=3D"COLOR: =
#000000"> </SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #000000">int</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #ff00ff">identity</SPAN><SPAN style=3D"COLOR: =
#000000">(</SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #800000">1</SPAN><SPAN=20
style=3D"COLOR: #000000">,</SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #800000">1</SPAN><SPAN=20
style=3D"COLOR: #000000">),<BR> </SPAN><SPAN=20
style=3D"COLOR: #ff0000">[</SPAN><SPAN style=3D"COLOR: =
#ff0000">value</SPAN><SPAN=20
style=3D"COLOR: #ff0000">]</SPAN><SPAN style=3D"COLOR: =
#000000"> </SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #000000">varchar</SPAN><SPAN=20
style=3D"COLOR: #000000">(</SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #800000">8000</SPAN><SPAN=20
style=3D"COLOR: #000000">)</SPAN><SPAN style=3D"FONT-SIZE: 10pt"><SPAN=20
style=3D"FONT-SIZE: 10pt"><SPAN style=3D"FONT-SIZE: 10pt"><SPAN=20
style=3D"COLOR: #008080"> -- =
NVarChar(4000)</SPAN></SPAN></SPAN></SPAN><BR><SPAN=20
style=3D"COLOR: #000000">)<BR></SPAN><SPAN style=3D"COLOR: =
#0000ff">AS</SPAN><SPAN=20
style=3D"COLOR: #000000"><BR></SPAN><SPAN style=3D"COLOR: =
#0000ff">BEGIN</SPAN><SPAN=20
style=3D"COLOR: #000000"><BR> </SPAN><SPAN=20
style=3D"COLOR: #0000ff">DECLARE</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #008000">@CurrentIndex</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #000000">int</SPAN><SPAN=20
style=3D"COLOR: #000000">;<BR> </SPAN><SPAN=20
style=3D"COLOR: #0000ff">DECLARE</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #008000">@NextIndex</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #000000">int</SPAN><SPAN=20
style=3D"COLOR: #000000">;<BR> </SPAN><SPAN=20
style=3D"COLOR: #0000ff">DECLARE</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #008000">@ReturnText</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #000000">varchar</SPAN><SPAN=20
style=3D"COLOR: #000000">(</SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #800000">8000</SPAN><SPAN=20
style=3D"COLOR: #000000">);</SPAN><SPAN style=3D"FONT-SIZE: 10pt"><SPAN=20
style=3D"FONT-SIZE: 10pt"><SPAN style=3D"FONT-SIZE: 10pt"><SPAN=20
style=3D"FONT-SIZE: 10pt"><SPAN style=3D"COLOR: #008080">--=20
NVarChar(4000)</SPAN></SPAN></SPAN></SPAN></SPAN><BR><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #0000ff">SELECT</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #008000">@CurrentIndex</SPAN><SPAN=20
style=3D"COLOR: #808080">=3D</SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #800000">1</SPAN><SPAN=20
style=3D"COLOR: #000000">;<BR> </SPAN><SPAN=20
style=3D"COLOR: #0000ff">WHILE</SPAN><SPAN style=3D"COLOR: =
#000000">(</SPAN><SPAN=20
style=3D"COLOR: #008000">@CurrentIndex</SPAN><SPAN=20
style=3D"COLOR: #808080"><=3D</SPAN><SPAN=20
style=3D"COLOR: #ff00ff">datalength</SPAN><SPAN=20
style=3D"COLOR: #000000">(</SPAN><SPAN=20
style=3D"COLOR: #008000">@SplitString</SPAN><SPAN=20
style=3D"COLOR: #000000">)) </SPAN><SPAN style=3D"FONT-SIZE: =
10pt"><SPAN=20
style=3D"FONT-SIZE: 10pt"><SPAN style=3D"FONT-SIZE: 10pt"><SPAN=20
style=3D"FONT-SIZE: 10pt"><SPAN style=3D"FONT-SIZE: 10pt"><SPAN=20
style=3D"COLOR: #008080">--=20
DATALENGTH(@SplitString)/2</SPAN></SPAN></SPAN></SPAN></SPAN></SPAN><BR><=
SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #0000ff">BEGIN</SPAN><SPAN=20
style=3D"COLOR: =
#000000"><BR> </SPAN><SPAN=
=20
style=3D"COLOR: #0000ff">SELECT</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #008000">@NextIndex</SPAN><SPAN=20
style=3D"COLOR: #808080">=3D</SPAN><SPAN=20
style=3D"COLOR: #ff00ff">charindex</SPAN><SPAN=20
style=3D"COLOR: #000000">(</SPAN><SPAN=20
style=3D"COLOR: #008000">@Separator</SPAN><SPAN=20
style=3D"COLOR: #000000">,</SPAN><SPAN=20
style=3D"COLOR: #008000">@SplitString</SPAN><SPAN=20
style=3D"COLOR: #000000">,</SPAN><SPAN=20
style=3D"COLOR: #008000">@CurrentIndex</SPAN><SPAN=20
style=3D"COLOR: =
#000000">);<BR> </SPAN><SP=
AN=20
style=3D"COLOR: #0000ff">IF</SPAN><SPAN style=3D"COLOR: =
#000000">(</SPAN><SPAN=20
style=3D"COLOR: #008000">@NextIndex</SPAN><SPAN=20
style=3D"COLOR: #808080">=3D</SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #800000">0</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN style=3D"COLOR: =
#808080">OR</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #008000">@NextIndex</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN style=3D"COLOR: =
#0000ff">IS</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #0000ff">NULL</SPAN><SPAN=20
style=3D"COLOR: =
#000000">)<BR>  =
; </SPAN><SPAN=20
style=3D"COLOR: #0000ff">SELECT</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #008000">@NextIndex</SPAN><SPAN=20
style=3D"COLOR: #808080">=3D</SPAN><SPAN=20
style=3D"COLOR: #ff00ff">datalength</SPAN><SPAN=20
style=3D"COLOR: #000000">(</SPAN><SPAN=20
style=3D"COLOR: #008000">@SplitString</SPAN><SPAN=20
style=3D"COLOR: #000000">)</SPAN><SPAN style=3D"COLOR: =
#808080">+</SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #800000">1</SPAN><SPAN=20
style=3D"COLOR: #000000">;</SPAN><SPAN style=3D"FONT-SIZE: 10pt"><SPAN=20
style=3D"FONT-SIZE: 10pt"><SPAN style=3D"FONT-SIZE: 10pt"><SPAN=20
style=3D"FONT-SIZE: 10pt"><SPAN style=3D"FONT-SIZE: 10pt"><SPAN=20
style=3D"FONT-SIZE: 10pt"><SPAN=20
style=3D"COLOR: =
#008080">--DATALENGTH(@SplitString)/2</SPAN></SPAN></SPAN></SPAN></SPAN><=
/SPAN></SPAN><BR><SPAN=20
style=3D"COLOR: =
#000000"> <BR> =
</SPAN><SPAN=20
style=3D"COLOR: #0000ff">SELECT</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #008000">@ReturnText</SPAN><SPAN=20
style=3D"COLOR: #808080">=3D</SPAN><SPAN=20
style=3D"COLOR: #ff00ff">substring</SPAN><SPAN=20
style=3D"COLOR: #000000">(</SPAN><SPAN=20
style=3D"COLOR: #008000">@SplitString</SPAN><SPAN=20
style=3D"COLOR: #000000">,</SPAN><SPAN=20
style=3D"COLOR: #008000">@CurrentIndex</SPAN><SPAN=20
style=3D"COLOR: #000000">,</SPAN><SPAN=20
style=3D"COLOR: #008000">@NextIndex</SPAN><SPAN=20
style=3D"COLOR: #808080">-</SPAN><SPAN=20
style=3D"COLOR: #008000">@CurrentIndex</SPAN><SPAN=20
style=3D"COLOR: =
#000000">);<BR><BR> </SPAN=
><SPAN=20
style=3D"COLOR: #0000ff">INSERT</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #0000ff">INTO</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #008000">@SplitStringsTable</SPAN><SPAN=20
style=3D"COLOR: #000000">(</SPAN><SPAN style=3D"COLOR: =
#ff0000">[</SPAN><SPAN=20
style=3D"COLOR: #ff0000">value</SPAN><SPAN style=3D"COLOR: =
#ff0000">]</SPAN><SPAN=20
style=3D"COLOR: =
#000000">)<BR> </SPAN><SPA=
N=20
style=3D"COLOR: #0000ff">VALUES</SPAN><SPAN style=3D"COLOR: =
#000000">(</SPAN><SPAN=20
style=3D"COLOR: #008000">@ReturnText</SPAN><SPAN=20
style=3D"COLOR: =
#000000">);<BR> <BR> =
</SPAN><SPAN=20
style=3D"COLOR: #0000ff">SELECT</SPAN><SPAN=20
style=3D"COLOR: #000000"> </SPAN><SPAN=20
style=3D"COLOR: #008000">@CurrentIndex</SPAN><SPAN=20
style=3D"COLOR: #808080">=3D</SPAN><SPAN=20
style=3D"COLOR: #008000">@NextIndex</SPAN><SPAN=20
style=3D"COLOR: #808080">+</SPAN><SPAN=20
style=3D"FONT-WEIGHT: bold; COLOR: #800000">1</SPAN><SPAN=20
style=3D"COLOR: #000000">;<BR> </SPAN><SPAN=20
style=3D"COLOR: #0000ff">END</SPAN><SPAN=20
style=3D"COLOR: #000000"><BR> </SPAN><SPAN=20
style=3D"COLOR: #0000ff">RETURN</SPAN><SPAN=20
style=3D"COLOR: #000000">;<BR></SPAN><SPAN=20
style=3D"COLOR: #0000ff">END</SPAN></DIV><BR> =20
=E6=9C=89=E6=97=B6=E6=88=91=E4=BB=AC=E6=8B=86=E5=88=86=E5=87=BA=E6=9D=A5=E8=
=BF=98=E6=98=AF=E9=9C=80=E8=A6=81=E5=BE=88=E9=95=BF=E7=9A=84=E5=AD=97=E7=AC=
=A6=E4=B8=B2=E6=9C=89=E5=8F=AF=E8=83=BD=E8=B6=85=E8=BF=87(N)VarChar=E7=9A=
=84=E9=95=BF=E5=BA=A6=EF=BC=8C=E5=BD=93=E7=84=B6=E4=B8=BA=E4=BA=86=E5=85=BC=
=E5=AE=B9SQL=20
Server2000=E4=B8=8D=E8=83=BD=E7=94=A8max=EF=BC=8C=E6=89=80=E4=BB=A5=E6=88=
=91=E4=BB=AC=E6=8B=86=E5=87=BA=E7=9A=84=E5=AD=97=E7=AC=A6=E4=B8=B2=E8=BF=98=
=E6=98=AF=E8=A6=81=E7=94=A8(N)Text=E6=9D=A5=E8=A1=A8=E7=A4=BA=EF=BC=8C<SP=
AN=20
style=3D"COLOR: =
red">=E9=9C=80=E8=A6=81=E6=B3=A8=E6=84=8F=E7=9A=84=E6=98=AF=E5=9C=A8=E5=B1=
=80=E9=83=A8=E5=8F=98=E9=87=8F=E4=B8=AD=E4=B8=8D=E8=83=BD=E5=AE=9A=E4=B9=89=
(N)Text=E7=9A=84=E7=B1=BB=E5=9E=8B=EF=BC=8C=E4=B8=8D=E8=BF=87=E6=88=91=E4=
=BB=AC=E5=8F=AF=E4=BB=A5=E6=8A=8Asubstring=E5=87=BA=E6=9D=A5=E7=9A=84=E5=AD=
=97=E7=AC=A6=E4=B8=B2=E7=9B=B4=E6=8E=A5=E5=8A=A0=E5=85=A5=E5=88=B0=E8=A1=A8=
=E5=8F=98=E9=87=8F=E4=B8=AD=EF=BC=8C=E8=80=8C=E4=B8=8D=E8=A6=81=E4=BB=98=E5=
=80=BC=E5=90=8E=E5=9C=A8Insert=E3=80=82</SPAN><BR><BR> =
2.=E5=BD=93=E6=88=91=E4=BB=AC=E4=BC=A0=E5=85=A5=E7=9A=84(N)VarChar=E6=97=B6=
=EF=BC=8C=E6=88=91=E4=BB=AC=E5=8F=AF=E4=BB=A5=E7=94=A8LEN=E6=9D=A5=E8=AE=A1=
=E7=AE=97=E9=95=BF=E5=BA=A6=EF=BC=8C=E5=80=BC=E5=BE=97=E6=B3=A8=E6=84=8F=E7=
=9A=84=E6=98=AFNVarChar=E7=9A=84=E6=9C=80=E5=A4=A7=E9=95=BF=E5=BA=A6=E6=98=
=AF4000=EF=BC=8C=E8=80=8CVarChar=E7=9A=84=E6=9C=80=E5=A4=A7=E9=95=BF=E5=BA=
=A6=E6=98=AF8000=E3=80=82=E4=B8=8B=E9=9D=A2=E6=88=91=E4=BB=AC=E6=9D=A5=E7=
=9C=8B=E4=B8=80=E4=B8=8B=E4=BB=A3=E7=A0=81=EF=BC=8C=E5=92=8C=E4=B8=8A=E9=9D=
=A2=E7=9A=84=E4=BB=A3=E7=A0=81=E5=9F=BA=E6=9C=AC=E6=B2=A1=E4=BB=80=E4=B9=88=
=E5=B7=AE=E5=88=AB=E3=80=82<BR><BR>
<DIV=20
style=3D"BORDER-RIGHT: #cccccc 1px solid; PADDING-RIGHT: 5px; =
BORDER-TOP: #cccccc 1px solid; PADDING-LEFT: 4px; FONT-SIZE: 13px; =
PADDING-BOTTOM: 4px; BORDER-LEFT: #cccccc 1px solid; WIDTH: 98%; =
PADDING-TOP: 4px; BORDER-BOTTOM: #cccccc 1px solid; BACKGROUND-COLOR: =
#eeeeee"><!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
--><SPAN style=3D"COLOR: #008080">--</SPAN><SPAN=20
style=3D"COLOR: =
#008080"> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
</SPAN><SPAN=20
style=3D"COLOR: #008080"><BR>-⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -