T-SQL: search by tags - 0 views

• 
  #1 Sergey Leifer on 07 Aug 10

   

  DECLARE @filter TABLE (id INT IDENTITY, data nvarchar(32))
  insert into @filter (data) values ('sql,performance')
  insert into @filter (data) values ('profiler')
  insert into @filter (data) values ('table,performance,view')
  
  DECLARE @blog TABLE (id INT IDENTITY, name nvarchar(20), tags nvarchar(20))
  insert into @blog (name,tags) values ('post 1','sql,profiler,table')
  insert into @blog (name,tags) values ('post 2','sql,performance')
  insert into @blog (name,tags) values ('post 3','profilter')
  insert into @blog (name,tags) values ('post 4','view,table')
  
  ;with cte
  as( select n=1,
  id,
  tag=case charindex(',',tags)
  when 0 then tags
  else substring(tags,1,charindex(',',tags,1)-1) end ,
  txt=substring(tags,charindex(',',tags,2)+1,len(tags))
  from @blog
  union all
  select n=n+1,
  id,
  tag=case charindex(',',txt)
  when 0 then txt
  else substring(txt,1,charindex(',',txt,1)-1) end ,
  txt=substring(txt,charindex(',',txt,2)+1,len(txt))
  from cte
  where txt!=tag
  )
  select
  f.id,
  f.data,
  b.[name],
  b.tags,
  RELEVANCE=count(*)
  from
  cte join
  @blog b on b.id=cte.id join
  @filter f on charindex(','+cte.tag+',' , ','+f.data+',')>0
  group by
  f.id,
  f.data,
  b.[name],
  b.tags
  order by
  id,
  count(*)desc,
  b.[name]

   

  <div class="cArrow"> </div><div class="cContentInner">DECLARE @filter TABLE (id INT IDENTITY, data nvarchar(32)) <br /> insert into @filter (data) values ('sql,performance') <br /> insert into @filter (data) values ('profiler') <br /> insert into @filter (data) values ('table,performance,view') <br /> <br /> DECLARE @blog TABLE (id INT IDENTITY, name nvarchar(20), tags nvarchar(20)) <br /> insert into @blog (name,tags) values ('post 1','sql,profiler,table') <br /> insert into @blog (name,tags) values ('post 2','sql,performance') <br /> insert into @blog (name,tags) values ('post 3','profilter') <br /> insert into @blog (name,tags) values ('post 4','view,table') <br /> <br /> ;with cte <br /> as( select n=1, <br /> id, <br /> tag=case charindex(',',tags) <br /> when 0 then tags <br /> else substring(tags,1,charindex(',',tags,1)-1) end , <br /> txt=substring(tags,charindex(',',tags,2)+1,len(tags)) <br /> from @blog <br /> union all <br /> select n=n+1, <br /> id, <br /> tag=case charindex(',',txt) <br /> when 0 then txt <br /> else substring(txt,1,charindex(',',txt,1)-1) end , <br /> txt=substring(txt,charindex(',',txt,2)+1,len(txt)) <br /> from cte <br /> where txt!=tag <br /> ) <br /> select <br /> f.id, <br /> f.data, <br /> b.[name], <br /> b.tags, <br /> RELEVANCE=count(*) <br /> from <br /> cte join <br /> @blog b on b.id=cte.id join <br /> @filter f on charindex(','+cte.tag+',' , ','+f.data+',')>0 <br /> group by <br /> f.id, <br /> f.data, <br /> b.[name], <br /> b.tags <br /> order by <br /> id, <br /> count(*)desc, <br /> b.[name]</div>

  ...

  Cancel

To Top