SQL自訂函數split分隔字串

標籤：擷取   arc   字串拆分   select   UNC   max   location   cti   bst   

一、F_Split：分割字串拆分為資料表

Create FUNCTION [dbo].[F_Split] (     @SplitString nvarchar(max),  --源字串     @Separator nvarchar(10)=‘ ‘  --分隔字元號，預設為空白格 ) RETURNS @SplitStringsTable TABLE  --輸出的資料表 (     [id] int identity(1,1),     [value] nvarchar(max) ) AS BEGIN     DECLARE @CurrentIndex int;     DECLARE @NextIndex int;     DECLARE @ReturnText nvarchar(max);     SELECT @CurrentIndex=1;     WHILE(@CurrentIndex<=len(@SplitString))         BEGIN             SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);             IF(@NextIndex=0 OR @NextIndex IS NULL)                 SELECT @NextIndex=len(@SplitString)+1;                 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);                 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);                 SELECT @CurrentIndex[email protected]+1;             END     RETURN; END  --使用樣本select * FROm dbo.F_Split(‘111,b2222,323232,32d,e,323232f,g3222‘, ‘,‘)

結果為

id          value

-------- ---------------------------------------

1           111

2           b2222

3           323232

4           32d

5           e

6           323232f

7           g3222

 

=========================================================================

二、F_SplitLength：擷取分割後的字元數組的長度

Create function [dbo].[F_SplitLength] (  @String nvarchar(max),  --要分割的字串  @Split nvarchar(10)  --分隔字元號 ) returns int as begin  declare @location int  declare @start int  declare @length int    set @String=ltrim(rtrim(@String))  set @location=charindex(@split,@String)  set @length=1  while @location<>0  begin    set @[email protected]+1    set @location=charindex(@split,@String,@start)    set @[email protected]+1  end  return @length end--調用樣本select dbo.F_SplitLength(‘111,b2222,323232,32d,e,323232f,g3222‘,‘,‘)

結果為7。

 

=========================================================================

三、F_SplitOfIndex：擷取分割後特定索引的字串

Create function [dbo].[F_SplitOfIndex] (  @String nvarchar(max),  --要分割的字串  @split nvarchar(10),  --分隔字元號  @index int --取第幾個元素 ) returns nvarchar(1024) as begin  declare @location int  declare @start int  declare @next int  declare @seed int    set @String=ltrim(rtrim(@String))  set @start=1  set @next=1  set @seed=len(@split)     set @location=charindex(@split,@String)    while @location<>0 and @index>@next  begin    set @[email protected]+@seed    set @location=charindex(@split,@String,@start)    set @[email protected]+1  end  if @location =0 select @location =len(@String)+1     return substring(@String,@start,@location-@start) end--使用樣本select dbo.F_SplitOfIndex(‘111,b2222,323232,32d,e,323232f,g3222‘,‘,‘, 3)

結果為323232。

 

轉自：http://www.cnblogs.com/xiaofengfeng/archive/2012/06/01/2530930.html

SQL自訂函數split分隔字串