Sql Server 拆分字符串函数
1 CREATE FUNCTION uf_StrSplit '1.1.2.50','.'
2 (@origStr varchar(7000), --待拆分的字符串
3 @markStr varchar(100)) --拆分标记，如','
4 RETURNS @splittable table
5 (
6 str_id varchar(4000) NOT NULL, --编号ID
7 string varchar(2000) NOT NULL --拆分后的字符串
8 )
9 AS
10 BEGIN
11 declare @strlen int,@postion int,@start int,@sublen int,@TEMPstr varchar(200),@TEMPid int
12 SELECT @strlen=LEN(@origStr),@start=1,@sublen=0,@postion=1,@TEMPstr='',@TEMPid=0
13
14 if(RIGHT(@origStr,1)<>@markStr )
15 begin
16 set @origStr = @origStr + @markStr
17 end
18 WHILE((@postion<=@strlen) and (@postion !=0))
19 BEGIN
20 IF(CHARINDEX(@markStr,@origStr,@postion)!=0)
21 BEGIN
22 SET @sublen=CHARINDEX(@markStr,@origStr,@postion)-@postion;
23 END
24 ELSE
25 BEGIN
26 SET @sublen=@strlen-@postion+1;
27
28 END
29 IF(@postion<=@strlen)
30 BEGIN
31 SET @TEMPid=@TEMPid+1;
32 SET @TEMPstr=SUBSTRING(@origStr,@postion,@sublen);
33 INSERT INTO @splittable(str_id,string) values(@TEMPid,@TEMPstr)
34 IF(CHARINDEX(@markStr,@origStr,@postion)!=0)
35 BEGIN
36 SET @postion=CHARINDEX(@markStr,@origStr,@postion)+1
37 END
38 ELSE
39 BEGIN
40 SET @postion=@postion+1
41 END
42 END
43 END
44 RETURN
45 END
46
47
例如：
1 select * from uf_StrSplit('1，1，2，50','，')
输出结果：str_id string
1 1
2 1
3 2
4 50
