Tag Search and Tag Cloud Search

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Tag Search and Tag Cloud Search

Author

Topic

ilayaraja.ajsquare
Starting Member

8 Posts

Posted - 2008-04-14 : 09:24:05

--set ANSI_NULLS ON
--set QUOTED_IDENTIFIER ON
--go
--
--
--ALTER PROC [dbo].[spEventTagCloud]
--as
--BEGIN
DECLARE @RECORDCOUNT INT;
DECLARE @SearchString varchar(2000);
DECLARE @QRY VARCHAR(2000);
DECLARE @SE VARCHAR(2000);
SELECT @RECORDCOUNT=COUNT(*) FROM TBEVENTS
DECLARE @ST INT;
SET @ST=1;
CREATE TABLE #TEMP2
(
MYTAGS VARCHAR(2000)
)
--CREATE TABLE #TEMP3
--(
-- TAGCOUNT INT
--)
CREATE TABLE #TEMP1
(
STR1 VARCHAR(2000)
)
WHILE @ST<@RECORDCOUNT
BEGIN
SET @QRY='SELECT TOP ' +CONVERT(VARCHAR,@ST)+' EVENTTAG FROM TBEVENTS'
--PRINT @QRY
INSERT INTO #TEMP1 EXEC (@QRY)
SELECT @SEARCHSTRING=STR1 FROM #TEMP1
SET @ST=@ST+1
declare @i1 int;
declare @i2 int;
declare @MatchType int ;
set @MatchType=0;
declare @Word varchar(100);
declare @Words table (Word varchar(100) not null);
declare @WordCount as integer;
DECLARE @TEMPWORD VARCHAR(2000);
begin
set nocount on
if (@MatchType != 2)
begin
set @SearchString = ' ' + @SearchString + ',';
--print 'Search String is :::: '+ @SearchString
set @i1 = 1;
while (@i1 != 0)
begin
set @i2=charindex(',', @SearchString, @i1+1)
--print @i1
if (@i2 != 0)
begin
set @Word = rtrim(ltrim(substring(@SearchString, @i1+1, @i2-@i1)))

SET @TEMPWORD=@WORD;
SET @TEMPWORD=REPLACE(@TEMPWORD,',','')
INSERT INTO #TEMP2 SELECT @TEMPWORD
--print 'Search WORD is :::: '+ @WORD

if @Word != '' insert into @Words select replace(@Word,',','')
end
set @i1 = @i2
end
end
else
insert into @Words select ltrim(rtrim(@SearchString))
set @WordCount = (select count(*) from @Words)
Declare @wordtemp varchar(2000);
set @wordtemp=@word
set @wordtemp=replace(@word,',','')
--INSERT INTO #TEMP2 SELECT @WORDtemp
END
END
SELECT mytags'Tag' , count(mytags)'Count' FROM #TEMP2 group by mytags ORDER BY [COUNT] DESC
--SELECT * FROM #TEMP1
DROP TABLE #TEMP1
DROP TABLE #TEMP2
--DROP TABLE #TEMP3
--END

________________________________
THE ABOVE EXAMPLE FOR A TAG CLOUD
MY TAGS ARE AS FOLLOW

EVENTTAG
_________
ASP.NET, C#, VB.NET
WELCOME TO ASP.NET
ASP.NET BOOKS,C#.NET BOOKS

I WOULD LIKE TO SELECT ALL COLUMNS FROM MY TABLE SEARCH TAG IS ASP.NET
THE FOLLOWING CODE WILL GENERATE AND SPLIT IT AS

TAG
____
ASP.NET
c#
VB.NET
WELCOME TO ASP.NET
ASP.NET BOOKS
C#.NET BOOKS

HOW TO SEARCH AN EXACT MATCH AS ASP.NET THE ONLY ONE ROW

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 09:28:27

Dup post:-

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=23

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-14 : 09:40:36

quote:
Originally posted by visakh16

Dup post:-

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=23

That should be http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100915

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 09:55:37

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

Dup post:-

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=23

That should be http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100915

Madhivanan

Failing to plan is Planning to fail

sorry copy paste error

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources