34,575
社区成员
发帖
与我相关
我的任务
分享
select top 1000 id=identity(int,1,1) into 序数表 from syscolumns a,syscolumns b
alter table 序数表 add constraint pk_id_序数表 primary key(id)
go
--示例数据
create table tbl1(id int,keywords nvarchar(1000))
insert tbl1 select 1,'kw1;kw2;kw3'
union all select 2,'kw2;kw3'
union all select 3,'kw3;kw1;kw4'
go
--第一种统计(计数)
select keyword=left(substring(a.keywords,b.id,charindex(';',a.keywords+';',b.id)-b.id),10)
,[count]=count(distinct a.id)
from tbl1 a,序数表 b
where b.id<=len(a.keywords)
and substring(';'+a.keywords,b.id,1)=';'
group by substring(a.keywords,b.id,charindex(';',a.keywords+';',b.id)-b.id)
go
/*
keyword count
---------- -----------
kw1 2
kw2 2
kw3 3
kw4 1
(4 行受影响)
*/
drop table 序数表,tbl1
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-03 15:43:05
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[name] varchar(14))
insert [tb]
select 1,'张三,李四,王五'
--------------开始查询--------------------------
--方法一 ,判断,的数量
select id,charindex(',',[name]) as 数量 from tb
--方法二
select
*,数量 = len([name]) - len(replace([name],',',''))
from
tb
----------------结果----------------------------
/*id 数量
----------- -----------
1 3
(1 行受影响)
*/
Declare @Temp Table
(
ID int,
[Name] varchar(50)
)
Insert Into @Temp Select 1,'张三,李四,王五'
Select ID, charindex(',',[Name]) from @Temp
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--date :2003-10-14
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
select count(*) from dbo.f_split('张三,李四,王五 ',',')
-----------
3
(所影响的行数为 1 行)
declare @str varchar(500);
set @str = '张三,李四,王五'
select len(@str) - len(replace(@str,',','')) + 1
/*
-----------
3
(1 row(s) affected)
*/
selct id,charindex(',',name)+1 as num from tb
select *,[count] = len(name) - len(replace(name,',',''))
from tb