SQL字符串处理--按分割符拆分字符串

SQL字符串处理--按分割符拆分字符串

例1
实现如下功能
name     data
a|b|c    ...
a|b      ...
a|c      ...
d        ...
统计后
a      1.3 
b      0.83
c      0.83
d      1

也就是  按名字统计 每条记录 按人头平均 统计 a (1/3+1/2+1/2)=1.3

多谢
--示例

 

--示例数据
create table tb(name varchar(10))
insert tb select 'a|b|c'
union all select 'a|b'
union all select 'a|c'
union all select 'd'
go

--统计处理
select top 8000 id=identity(int) into # from syscolumns a,syscolumns b
select name,total=cast(sum(cnt) as decimal(10,2))
from(
select 
name=substring(a.name,b.id,charindex('|',a.name+'|',b.id)-b.id),
cnt=1./(len(a.name)-len(replace(a.name,'|',''))+1)
from tb a,# b
where 
substring('|'+a.name,b.id,1)='|'
and 
len(a.name)>=b.id
)a 
group by name
order by name
drop table #
go

--删除测试
drop table tb

/*--结果

name       total       
---------- ------------
a          1.33
b          .83
c          .83
d          1.00

例2:
--示例数据

--辅助表法

create table tb(id int,name varchar(8000))
insert tb select 1,',1,3,4,'
union all select 2,',12,34,67,89,'
go

--转换处理
select top 8000 id=identity(int) into # from syscolumns a,syscolumns b
select a.id,name=substring(a.name,b.id,charindex(',',a.name+',',b.id)-b.id)
from tb a,# b
where len(a.name)>b.id
 and substring(','+a.name,b.id,1)=','
 and substring(a.name,b.id,1)<>','
order by a.id,b.id
drop table #
go

 

--删除测试
drop table tb

/*--结果

id          name        
----------- -------------
1           1
1           3
1           4
2           12
2           34
2           67
2           89

（所影响的行数为 7 行）
--*/

--动态SQL法

declare @a table (id int,n nvarchar(100))
insert into @a select 1,',1,3,4,'
insert into @a select 2,',12,34,67,89,'
declare @s nvarchar(4000)
set @s=''
select @s=@s+replace(left(n,len(n)-1),',','union all select '+quotename(id,'''')+',') from @a
set @s=stuff(@s,1,10,'')
print @s
exec(@s)

例3:循环截取法
假设table1表中有字段col1
col的一行存有如下数据1,2,3,4,5,6,7,8
col1中的数据用逗号分割
请问如何提取成如下形式的数据
1
2
3
4
5
6
7
8

--创建函数

create function f_split(@str varchar(8000))
returns @tb table(num int)
as
begin
      while charindex(',',@str)>0
      begin
            insert @tb
            select left(@str,charindex(',',@str)-1)

            set @str=stuff(@str,1,charindex(',',@str),'')
      end
      
      insert @tb select @str
      return
end 
go

 

--测试
declare @s varchar(100)
select @s='1,2,3,4,5,6,7,8'
select * from f_split(@s)

--结果
/*
num        
-----------
1
2
3
4
5
6
7
8

（所影响的行数为 8 行）
*/
'上火温度' ,'中火温度' ,'下火温度'

通过select 语句如果把以上三个字符串作为一个字段值返回,结果如下:

   温度
 上火温度
 中火温度
 下火温度

create function f_tb(@str varchar(8000))
returns @tb table([温度] varchar(20))
as
begin
       while charindex(',',@str)>0
       begin
             insert @tb
             select left(@str,charindex(',',@str)-1)
             set @str=stuff(@str,1,charindex(',',@str),'')
       end

       insert @tb select @str
       return
end
go

declare @str varchar(8000)
set @str='上火温度,中火温度,下火温度'
select * from f_tb(@str)

 

/*
温度                  
--------------------
上火温度
中火温度
下火温度

（所影响的行数为 3 行）
*/

例4
Result
2
2,5
2,3
2,3,4
3,5

我想得到这样的结果:
Result  Num
2       4
3       2
4       1
5       2

SQL语句该怎么写，在线等，立即结帖~
--通用的

--示例数据
create table tb(Result varchar(10))
insert tb select '2'
union all select '2,5'
union all select '2,3'
union all select '2,3,4'
union all select '3,5'
go

--统计处理

declare @i int
select @i=max(len(Result)) from tb
set rowcount @i
select id=identity(int) into #t from syscolumns a,syscolumns b
set rowcount 0
select Result,Num=count(*)
from(
select Result=substring(a.Result,b.id,charindex(',',a.Result+',',b.id)-b.id)
from tb a,#t b
where len(a.Result)>=b.id and substring(','+a.Result,b.id,1)=','
)a group by Result
drop table #t
go

 

--删除测试
drop table tb

/*--测试结果
例5: zjcxc书中的例子:

--各种字符串分函数

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


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

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