轉載：分隔數組返回table

-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter FUNCTION dbo.fn_Split(@string NVARCHAR(MAX), @separator NCHAR(1))
RETURNS @table TABLE
(CODE int identity(1,1),
[NAME] VARCHAR(50)
)
AS
BEGIN
declare @item varchar(50)
declare @m int
declare @n int
declare @i int

Set @m=CHARINDEX(@separator,@string)  
Set @n=1  
Set @i=0

WHILE  @m>0  
    BEGIN  
        Set @item=SubString(@string,@n,@m-@n)  
  insert into @table([NAME])
  values(@item)
    Set @i=@i+1  
    Set @n=@m+1       
    Set @m=CHARINDEX(@separator,@string,@n) 
 END
return
END
GO

select * from dbo.fn_Split('a.b.c.d','.')

 

/*
--函數具體用法：
--最常用的調用：
select *
from dbo.RecurrentSplit('AA@BBB@CCC@','@',0,0)

select *
from dbo.RecurrentSplit('AA@@@BBB@@@CCC@@@','@@@',null,null)--默認

--具體細節用法：
--分割字元不顯示，分割方法是按前面的分割字串,只顯示分割後的字串
select *
from dbo.RecurrentSplit('AA@@@BBB@@@CCC@@@','@@@',0,1)
--分割字元顯示,並是按前面的來進行分割，分割字元放在被分割字串的前面
select *
from dbo.RecurrentSplit('@@@AA@@@BBB@@@CCC@@@','@@@',1,1)
--分割字元不顯示,並是按後面的來進行分割，只顯示分割後的字串
select *
from dbo.RecurrentSplit('AA@@@BBB@@@CCC@@@','@@@',0,0)
--分割字元顯示,並是按後面的來進行分割，分割字元放在被分割字串的後面
select *
from dbo.RecurrentSplit('AA@@@BBB@@@CCC@@@','@@@',1,0)
*/

 

USE [TESTDB]
GO
/****** 物件:  UserDefinedFunction [dbo].[RecurrentSplit]    指令碼日期: 07/30/2009 17:01:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

select * from [dbo].[RecurrentSplit]('test1,test2,test3',',',1,1)
ALTER FUNCTION [dbo].[RecurrentSplit]
(
@nvStr nvarchar(2000)  --需要分割字符串
,@vSeparte varchar(50)  --分割字符串
,@iIsHaveSeparte int   --是否显示字符串
,@iIsBefore int    --是否是后面的分割符（分割字符分割的顺序）
)
RETURNS @Split table
(
IndexNo int default(0)   --流水号
,SplitName nvarchar(1000)  --分割后字符串
)
AS
BEGIN
 if(charindex(@vSeparte,@nvStr)<=0) --处理在整个字符串里都没有要分割，也就是字符串本身
 begin
  insert into @Split(SplitName) values(@nvStr)
  return
 end
 declare @iSeparteLen int
 ,@iEndHave int --最后几个字符串是否是分割字符
 ,@iStartHave int --前面几个字符串是否是分割字符
 select @iSeparteLen=len(@vSeparte)
 ,@iStartHave=0
 ,@iEndHave=0
 ,@iIsHaveSeparte=case when @iIsHaveSeparte is null --默认值
  then 0
  else @iIsHaveSeparte
  end
 ,@iIsBefore=case when @iIsBefore is null --默认值
  then 0
  else @iIsBefore
  end
 if(@iIsBefore=1) --只有在处理前面字符串分割时才用
 begin
  if(left(@nvStr,@iSeparteLen)<>@vSeparte)--处理前面几个分割字符一定是分割字符，不是就加
  begin
   select @nvStr=@vSeparte+@nvStr
   ,@iStartHave=1
  end
 end
 if(right(@nvStr,@iSeparteLen)<>@vSeparte)--处理最后几个分割字符一定是分割字符，不是就加
 begin
  select @nvStr=@nvStr+@vSeparte
  ,@iEndHave=1
 end; --分号一定不能少，因为用了with,是用公用表达式，在其前面一定要加;
 
 with CharCET(CharStr,StrLen,IndexNo)
 as
 (
  select substring(@nvStr,0
  ,case when @iIsBefore=0 then charindex(@vSeparte,@nvStr)+@iSeparteLen
  else charindex(@vSeparte,@nvStr,charindex(@vSeparte,@nvStr)+@iSeparteLen) end
  ) CharStr
  ,case when @iIsBefore=0 then charindex(@vSeparte,@nvStr)
  else charindex(@vSeparte,@nvStr,charindex(@vSeparte,@nvStr)+@iSeparteLen) end StrLen
  ,0 IndexNo  --第一次初始化
  union all
  select substring(@nvStr
  ,case when @iIsBefore=0 then StrLen+@iSeparteLen
  else StrLen end
  ,charindex(@vSeparte,@nvStr,StrLen+@iSeparteLen)-StrLen) CharStr
  ,charindex(@vSeparte,@nvStr,StrLen+@iSeparteLen) StrLen
  ,IndexNo+1 IndexNo --进行递归分割字符串
  from CharCET
  where StrLen<len(@nvStr)-@iSeparteLen --处理递归结束语句，也就是不要让其生成无限弟归下去
 )
 insert into @Split(IndexNo,SplitName)
 select IndexNo,case when @iIsHaveSeparte=0
  then replace(CharStr,@vSeparte,'')
  else CharStr
  end CharStr
 from CharCET
 if(@iIsHaveSeparte=1) --是否显示分割字符串
 begin
  update @Split --处理前面的分割字符串
  set SplitName=case when @iStartHave=1
   then replace(SplitName,@vSeparte,'')
   else SplitName
   end
  where IndexNo = 0

  update @Split --处理后面的分割字符串
  set SplitName=case when @iEndHave=1
   then replace(SplitName,@vSeparte,'')
   else SplitName
   end
  where IndexNo = (select Max(IndexNo) from @Split)
 end
 RETURN
END