字符串处理--字符串拆分

--各种字符串分函数

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

--3.2.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

--3.2.3.1 使用临时性分拆辅助表法
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.2.3.2 使用永久性分拆辅助表法
--字符串分拆辅助表
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

 

 

 

/*==============================================*/
--3.2.4分拆数据到列
declare @t table(col varchar(50))
insert into @t select 'aa,ab,ac'
insert into @t select '1,2,3'

declare @i int,@s varchar(1000)
set @i=0
select col into #t from @t
while @@rowcount>0   --开始循环
 begin
  select @i=@i+1, @s='alter table #t add col' + cast(@i as varchar) +' varchar(10)'  --修改表结构，添加一列
 exec(@s)
 set @s=' update #t set col'+cast(@i as varchar)
  +'=left(col,charindex('','',col+'','')-1),
   col=stuff(col,1,charindex('','',col+'',''),'''')
  where col>'''''   --修改刚添加的那一列值,然后截断col列
 exec(@s)
end   --结束循环
--删除col列和最后一列
set @s='alter table #t drop column col,col'+cast(@i as varchar)

exec(@s)
select * from #t
drop table #t

 

 

/*==============================================*/

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

--3.2.5 将数据项按数字与非数字再次拆份
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),    --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(No varchar(100),Value varchar(20))
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 No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')),
  Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1))
 FROM(
  SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))
  FROM @t
  WHERE ID<=LEN(@s+'a')
   AND CHARINDEX(@split,@split+@s,ID)=ID)a
 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

 

 

 

--3.2.6 分拆短信数据
CREATE FUNCTION f_splitSTR(@s varchar(8000))
RETURNS @re TABLE(split varchar(10),value varchar(100))
AS
BEGIN
 DECLARE @splits TABLE(split varchar(10),splitlen as LEN(split))
 INSERT @splits(split)
 SELECT 'AC' UNION ALL
 SELECT 'BC' UNION ALL
 SELECT 'CC' UNION ALL
 SELECT 'DC' 
 DECLARE @pos1 int,@pos2 int,@split varchar(10),@splitlen int
 SELECT TOP 1
  @pos1=1,@split=split,@splitlen=splitlen
 FROM @splits
 WHERE @s LIKE split+'%'
 WHILE @pos1>0
 BEGIN
  SELECT TOP 1
   @pos2=CHARINDEX(split,@s,@splitlen+1)
  FROM @splits
  WHERE CHARINDEX(split,@s,@splitlen+1)>0
  ORDER BY CHARINDEX(split,@s,@splitlen+1)
  IF @@ROWCOUNT=0
  BEGIN
   INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,''))
   RETURN
  END
  ELSE
  BEGIN
   INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1))
   SELECT TOP 1
    @pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'')
   FROM @splits
   WHERE STUFF(@s,1,@pos2-1,'') LIKE split+'%'
  END
 END
 RETURN
END
GO