最近经常用到字符串的分割问题，这里把同事写的一个函数与大家共享。
1
--作成者：謝宏斌
2----機能：Varchar Change to Table
3----String = '1,2,3,4,5,6,7,8,'
4----Table
5-------- Value Location
6-----------------------------------------
7-------- 1 1
8-------- 2 3
9-------- 3 5
10--作成日：2007/1/25
11
12CREATE FUNCTION dbo.StringtoArray (@String varchar(255),@Flag varchar(1))
13RETURNS
14 @ArrayTable table
15 (
16 Value decimal,
17 Location int
18 )
19AS
20BEGIN
21 DECLARE @LEN INT
22 DECLARE @INDEX INT
23 DECLARE @LOCA INT
24
25 SET @LEN = LEN(@String)
26 SET @LOCA = 0
27 WHILE(@LEN > 0)
28 BEGIN
29 SET @INDEX = CHARINDEX(@Flag,@String)
30 SET @LOCA = @LOCA + CONVERT(INT,@INDEX)
31 insert into @ArrayTable values (CONVERT(decimal,left(@String,@INDEX-1)),@LOCA-1)
32 set @String = substring(@String,@INDEX+1,@LEN-@INDEX)
33 SET @LEN = LEN(@String)
34 END
35 RETURN
36END
37
38
39
40引用时：
41SELECT * from dbo.StringtoArray(@String,@Flag)
42
posted on 2007-07-03 12:55
TRE-China R&D 阅读(452)
评论(0) 编辑 收藏 引用 所属分类:
SQL