SQL字符串分割解析

浏览数：15 / 时间：2015年06月12日

--方法一：动态SQL法

declare @s varchar(50),@sql varchar(300)  
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

View Code

--方法二：循环截取法

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))
        --STUFF ( character_expression , start , length ,character_expression )  
        Set @s=Stuff(@s,1,CharIndex(@split,@s)+@splitlen,‘‘) 
        --Set @s = Substring(@s, CharIndex(@split,@s)+@splitle, 100) 
    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

View Code

--方法三：使用临时性分拆辅助表法

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

View Code

--方法四：使用永久性分拆辅助表法 --字符串分拆辅助表

SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR  
FROM syscolumns a,syscolumns b  
GO  
--字符串分拆处理函数  
Create function f_splitSTR(  
    @s     nvarchar(8000),  --待分拆的字符串  
    @split  nvarchar(10)     --数据分隔符  
)returns table  
As  
    return( 
        --cast类型转换函数 --CharIndex( exp1 , exp2 [,start_location])
        Select col=cast(SubString(@s,ID,CharIndex(@split,@s+@split,ID)-ID) as nvarchar(100))  
        From tb_splitSTR  Where ID<=len(@s+‘a‘) and CharIndex(@split,@split+@s,ID)=ID)  
GO

View Code

--方法五：利用sql server2005的OUTER APPLY

Create function [dbo].[fn_Split]  
(  
  @str nvarchar(max) ,  
  @split nvarchar(10)  
  return table  
  As   
  return  
    ( Select    B.id 
      --Convert (data_type[(length)], expression [, style]) 类型转换
      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  
    )