sql server中对字符串实现Split操作

先来一个函数，该函数将帮助你对字符串进行分割

 1 CREATE function [dbo].[SplitString]
 2 (
 3     @Input nvarchar(max),
 4     @Separator nvarchar(max)=',', 
 5     @RemoveEmptyEntries bit=1 
 6 )
 7 returns @TABLE table 
 8 (
 9     [Id] int identity(1,1),
10     [Value] nvarchar(max)
11 ) 
12 as
13 begin 
14     declare @Index int, @Entry nvarchar(max)
15     set @Index = charindex(@Separator,@Input)
16 
17     while (@Index>0)
18     begin
19         set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))
20         
21         if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
22             begin
23                 insert into @TABLE([Value]) Values(@Entry)
24             end
25 
26         set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))
27         set @Index = charindex(@Separator, @Input)
28     end
29     
30     set @Entry=ltrim(rtrim(@Input))
31     if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
32         begin
33             insert into @TABLE([Value]) Values(@Entry)
34         end
35 
36     return
37 end
38 GO
View Code

 

下面来点使用说明，仅供参考

1 declare @fields varchar(500)
2 set @fields = 'SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,rowguid,ModifiedDate'
3 select Id,Value from dbo.SplitString(@fields,',',1)
View Code

效果如下：（id，value都有，就不用担心如何取哪一行的数据）

 

 

posted @ 2013-08-02 15:02  hejiyong  阅读(257)  评论(0)  编辑  收藏  举报