日期:2014-05-18 浏览次数:20761 次
分段截取 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_GetStr] GO --分段截取函数 CREATE FUNCTION dbo.f_GetStr( @s varchar(8000), --包含多个数据项的字符串 @pos int, --要获取的数据项的位置 @split varchar(10) --数据分隔符 )RETURNS varchar(100) AS BEGIN IF @s IS NULL RETURN(NULL) DECLARE @splitlen int SELECT @splitlen=LEN(@split+'a')-2 WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0 SELECT @pos=@pos-1, @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'') RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),'')) END GO select dbo.f_GetStr('2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00 ',7,'∮')
------解决方案--------------------
select '汽车' as s,count(*) as ct from tb where replace(keys,'%',',') like '%汽车%' union all select '轮船' as s,count(*) as ct from tb where replace(keys,'%',',') like '%轮船%' union all select '大炮' as s,count(*) as ct from tb where replace(keys,'%',',') like '%大炮%' union all select '坦克' as s,count(*) as ct from tb where replace(keys,'%',',') like '%坦克%' union all select '火箭' as s,count(*) as ct from tb where replace(keys,'%',',') like '%火箭%' order by 2 desc
------解决方案--------------------
create table tb(id int,keys nvarchar(20)) insert into tb select 1,'汽车%轮船%轮船%大炮%坦克%火箭' insert into tb select 2,'轮船%大炮%坦克%火箭' insert into tb select 3,'大炮%坦克%火箭' insert into tb select 4,'轮船%轮船%大炮%坦克' insert into tb select 5,'坦克' go select '汽车' as s,count(*) as ct from tb where replace(keys,'%',',') like '%汽车%' union all select '轮船' as s,count(*) as ct from tb where replace(keys,'%',',') like '%轮船%' union all select '大炮' as s,count(*) as ct from tb where replace(keys,'%',',') like '%大炮%' union all select '坦克' as s,count(*) as ct from tb where replace(keys,'%',',') like '%坦克%' union all select '火箭' as s,count(*) as ct from tb where replace(keys,'%',',') like '%火箭%' order by 2 desc /* s ct ---- ----------- 坦克 5 大炮 4 火箭 3 轮船 3 汽车 1 (5 行受影响) */ go drop table tb
------解决方案--------------------
create table tb(id int,cname varchar(100)) insert into tb select 1 ,'汽车%轮船%轮船%大炮%坦克%火箭' union all select 2 ,'轮船%大炮%坦克%火箭' union all select 3 ,'大炮%坦克%火箭' union all select 4 ,'轮船%轮船%大炮%坦克' union all select 5 ,'坦克' go declare @jiansuo varchar(100) set @jiansuo = '汽车,轮船,轮船,大炮,坦克' select id,sum(case when charindex(cname,@jiansuo)>0 then 1 else 0 end) cnt from( select a.id,substring(a.cname,b.number,charindex('%',a.cname+'%',b.number)-b.number) cname from tb a,master..spt_values b where b.[type] = 'p' and b.number between 1 and len(a.cname) and substring('%'+a.cname,b.number,1) = '%' )t group by id order by cnt desc drop table tb /****************** id cnt ----------- ----------- 1 5 4 4 2 3 3 2 5 1 (5 行受影响)
------解决方案--------------------
create table tb(id int,cname varchar(100)) insert into tb select 1 ,'汽车%轮船%轮船%大炮%坦克%火箭' union all select 2 ,'轮船%大炮%坦克%火箭' union all select 3 ,'大炮%坦克%火箭' union all select 4 ,'轮船%轮船%大炮%坦克' union all select 5 ,'坦克' go declare @jiansuo varchar(100) set @jiansuo = '汽车,轮船,轮船,大炮,坦克' select id,sum(case wh