SQL字符串分割解析

--方法一:动态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 (NFN, NIF, NTF))  
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 (NFN, NIF, NTF))  
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 (NFN, NIF, NTF))  
drop function [dbo].[f_splitSTR]  
GO  
if exists (select * from dbo.sysobjects 
where id = object_id(N[dbo].[tb_splitSTR]) and objectproperty(id,NIsUserTable)=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  
    ) 
View Code

--备注说明:  方法4必须在sql server2005下才可以运行 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。