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

最新推荐文章于 2024-08-17 22:30:13 发布

惊世骇俗

最新推荐文章于 2024-08-17 22:30:13 发布

阅读量2.6k

点赞数

分类专栏： C/C++ 文章标签： sql insert table function object 测试

C/C++ 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

例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

多谢
--示例

-- 删除测试

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 语句如果把以上三个字符串作为一个字段值返回,结果如下:

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

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

（所影响的行数为 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.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