字符串分拆函数:
/**/
/****** 对象: UserDefinedFunction [dbo].[Split] 脚本日期: 04/23/2007 16:02:42 ******/
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
--
=============================================
--
Author: Librading
--
Create date:
--
Description: 字符串分拆函数
--
=============================================
CREATE
FUNCTION
[
dbo
]
.
[
Split
]
(
@Input
varchar
(
4000
),
--
输入字符串
@Separator
varchar
(
4000
)
=
'
,
'
--
分隔符
)
RETURNS
@Result
TABLE
(
Value
varchar
(
4000
)
)
AS
BEGIN
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
DECLARE
@InputLength
int
;
SET
@InputLength
=
LEN
(
@Input
);
DECLARE
@SeparatorLength
int
;
SET
@SeparatorLength
=
LEN
(
@Separator
);
DECLARE
@LastIndex
int
;
SET
@LastIndex
=
0
;
DECLARE
@CurrentIndex
int
;
SET
@CurrentIndex
=
-
1
;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
WHILE
@CurrentIndex
!=
0
BEGIN
SET
@CurrentIndex
=
CHARINDEX
(
@Separator
,
@Input
,
@LastIndex
);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
IF
@CurrentIndex
!=
0
INSERT
@Result
(
Value
)
VALUES
(
SUBSTRING
(
@Input
,
@LastIndex
,
@CurrentIndex
-
@LastIndex
)
)
ELSE
INSERT
@Result
(
Value
)
VALUES
(
SUBSTRING
(
@Input
,
@LastIndex
,
@InputLength
+
1
-
@LastIndex
)
)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SET
@LastIndex
=
@CurrentIndex
+
@SeparatorLength
;
END
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
RETURN
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
END
使用例子:
1、简单的例子:
select
*
from
dbo.Split(
'
a,b,c
'
,
'
,
'
);
2、拆分后的表作为 int 或者其他数据类型而不是字符串:
DECLARE
@Table
TABLE
(
ID
int
);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
INSERT
@Table
SELECT
*
FROM
[
dbo
]
.
[
Split
]
(
'
1---2---3---4---5
'
,
'
---
'
);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SELECT
*
FROM
@Table
;