存储过程中实现类似split功能(charindex)
1
create procedure sp_split_string
2
(
3
@string NVARCHAR(4000)
4
)
5
AS
6
BEGIN
7
DECLARE @object_id nvarchar(500)
8
DECLARE @i INT
9
DECLARE @len INT
10
print @string
11
IF (@string IS NULL) OR (LTRIM(@string) = '')
12
RETURN
13
14
WHILE CHARINDEX(',',@string) > 0
15
BEGIN
16
SET @len = LEN(@string)
17
SET @i = CHARINDEX(',', @string)
18
SET @object_id = LEFT(@string, @i-1)
19![](/Images/OutliningIndicators/None.gif)
20
INSERT INTO a (id) VALUES (@object_id)--少做修改,改成需要的sql语句即可
21
SET @string = RIGHT(@string, @len - @i)
22
END
23
SET @object_id = @string
24
INSERT INTO a (id) VALUES (@object_id)--少做修改,改成需要的sql语句即可
25
END
26
go
27![](/Images/OutliningIndicators/None.gif)
28
--测试
29
-- select * from a
30
-- exec sp_split_string '102,103,105,106,107,108,200,500,306,408'
31
-- select * from a
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
posted on 2005-11-25 11:10 Chandler.C 阅读(1152) 评论(0) 编辑 收藏 举报