转：SQL Server字符串拆分（split）

原文链接：https://www.iteye.com/blog/1017401036-2396054

这是在某个项目中完成某个功能时，遇到的一个小问题。

1、在创建视图时，遇到将特定规则的数字字符串拆分后并相加的问题，但是sql server中并没有根据某字符拆分成数组的方法，在网上找到并转载

比如：将 2/2/2/2 拆分后并相加

第一种拆分方法

--支持分割符多字节  
  
--使用方法 --select * from dbo.split('abc-def-ghi-jkl','-')  
  
--select * from dbo.split('abc----def----ghi----jkl','----')  
  
  
CREATE FUNCTION [dbo].[split](@Long_str varchar(max),@split_str varchar(100))  
RETURNS @tmp TABLE(  
ID int IDENTITY PRIMARY KEY,  
short_str varchar(max)  
)  
AS  
BEGIN  
DECLARE @long_str_Tmp varchar(max),  
@short_str varchar(max),  
@split_str_length int  
  
SET @split_str_length = LEN(@split_str)  
  
IF CHARINDEX(@split_str,@Long_str)=1  
     SET @long_str_Tmp=SUBSTRING(@Long_str,  
 @split_str_length+1,  
 LEN(@Long_str)-@split_str_length)  
  
ELSE  
     SET @long_str_Tmp=@Long_str  
  
IF CHARINDEX(REVERSE(@split_str),REVERSE(@long_str_Tmp))>1  
    SET @long_str_Tmp=@long_str_Tmp+@split_str  
ELSE  
    SET @long_str_Tmp=@long_str_Tmp  
  
WHILE CHARINDEX(@split_str,@long_str_Tmp)>0  
    BEGIN  
    SET @short_str=SUBSTRING(@long_str_Tmp,1,  
 CHARINDEX(@split_str,@long_str_Tmp)-1)  
    DECLARE @long_str_Tmp_LEN INT,@split_str_Position_END int  
    SET @long_str_Tmp_LEN = LEN(@long_str_Tmp)  
    SET @split_str_Position_END = LEN(@short_str)+@split_str_length  
    SET @long_str_Tmp=REVERSE(SUBSTRING(REVERSE(@long_str_Tmp),1,  
 @long_str_Tmp_LEN-@split_str_Position_END))  
    IF @short_str<>'' INSERT INTO @tmp SELECT @short_str  
    END  
RETURN  
END  

第二种拆分方法

--SQL Server Split函数  
--Author:zc_0101   
--说明：  
--支持分割符多字节  
--使用方法   
--Select * FROM DBO.F_SQLSERVER_SPLIT('1203401230105045','0')      
--select * from DBO.F_SQLSERVER_SPLIT('abc1234a12348991234','1234')   
--Select * from DBO.F_SQLSERVER_SPLIT('ABC',',')     
CREATE FUNCTION F_SQLSERVER_SPLIT(@Long_str NVARCHAR(MAX),@split_str NVARCHAR(100))      
RETURNS  @tmp TABLE(          
    ID          inT     IDENTITY PRIMARY KEY,        
    short_str   NVARCHAR(MAX)      
)      
AS     
BEGIN     
    DECLARE @short_str NVARCHAR(MAX),@split_str_length int,@split_str_Position_Begin int  
    SET @split_str_length = LEN(@split_str)   
    SET @Long_str=REPLACE(REPLACE(@Long_str,CHAR(10),''),CHAR(13),'')  
    IF CHARINDEX(@split_str,@Long_str)=1   
         SET @Long_str=STUFF(@Long_str,1,@split_str_length,'')  
    IF CHARINDEX(@split_str,@Long_str)=0  
        INSERT INTO @tmp SELECT @Long_str   
    ELSE  
        BEGIN  
            WHILE 1>0      
                BEGIN     
                    SET @split_str_Position_Begin = CHARINDEX(@split_str,@Long_str)  
                    SET @short_str=LEFT(@Long_str,@split_str_Position_Begin-1)   
                    IF @short_str<>'' INSERT INTO @tmp SELECT @short_str    
                    SET @Long_str=STUFF(@Long_str,1,@split_str_Position_Begin+@split_str_length-1,'')  
                    SET @split_str_Position_Begin = CHARINDEX(@split_str,@Long_str)  
                    IF @split_str_Position_Begin=0   
                    BEGIN  
                        IF LTRIM(@Long_str)<>''  
                            INSERT INTO @tmp SELECT @Long_str   
                        BREAK  
                    END  
                END             
        END  
    RETURN       
END  

汇总的拆分方法

--方法0：动态SQL法  
declare @s varchar(100),@sql varchar(1000)  
set @s='1,2,3,4,5,6,7,8,9,10'  
set @sql='select col='''+ replace(@s,',',''' union all select ''')+''''  
PRINT @sql  
exec (@sql)   
  
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))  
drop function [dbo].[f_splitSTR]  
GO  
--方法1：循环截取法  
CREATE FUNCTION f_splitSTR(  
@s   varchar(8000),   --待分拆的字符串  
@split varchar(10)     --数据分隔符  
)RETURNS @re TABLE(col varchar(100))  
AS  
BEGIN  
 DECLARE @splitlen int  
 SET @splitlen=LEN(@split+'a')-2  
 WHILE CHARINDEX(@split,@s)>0  
 BEGIN  
  INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))  
  SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')  
 END  
 INSERT @re VALUES(@s)  
 RETURN  
END  
GO   
  
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))  
drop function [dbo].[f_splitSTR]  
GO  
--方法2：使用临时性分拆辅助表法  
CREATE FUNCTION f_splitSTR(  
@s   varchar(8000),  --待分拆的字符串  
@split varchar(10)     --数据分隔符  
)RETURNS @re TABLE(col varchar(100))  
AS  
BEGIN  
 --创建分拆处理的辅助表(用户定义函数中只能操作表变量)  
 DECLARE @t TABLE(ID int IDENTITY,b bit)  
 INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b  
  
 INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)  
 FROM @t  
 WHERE ID<=LEN(@s+'a')  
  AND CHARINDEX(@split,@split+@s,ID)=ID  
 RETURN  
END  
GO   
  
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))  
drop function [dbo].[f_splitSTR]  
GO  
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)  
drop table [dbo].[tb_splitSTR]  
GO  
--方法3：使用永久性分拆辅助表法  
--字符串分拆辅助表  
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR  
FROM syscolumns a,syscolumns b  
GO  
--字符串分拆处理函数  
CREATE FUNCTION f_splitSTR(  
@s     varchar(8000),  --待分拆的字符串  
@split  varchar(10)     --数据分隔符  
)RETURNS TABLE  
AS  
RETURN(  
 SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))  
 FROM tb_splitSTR  
 WHERE ID<=LEN(@s+'a')  
  AND CHARINDEX(@split,@split+@s,ID)=ID)  
GO   
  
--方法4：利用sql server2005的OUTER APPLY  
--备注说明：必须在sql server2005下才可以运行  
CREATE FUNCTION [dbo].[ufn_SplitStringToTable]  
(  
  @str VARCHAR(MAX) ,  
  @split VARCHAR(10)  
)  
RETURNS TABLE  
    AS  
RETURN  
    ( SELECT    B.id  
      FROM      ( SELECT    [value] = CONVERT(XML , '<v>' + REPLACE(@str , @split , '</v><v>')  
                            + '</v>')  
                ) A  
      OUTER APPLY ( SELECT  id = N.v.value('.' , 'varchar(100)')  
                    FROM    A.[value].nodes('/v') N ( v )  
                  ) B  
    )