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.
Author |
Topic |
ilayaraja.ajsquare
Starting Member
8 Posts |
Posted - 2008-04-14 : 09:25:33
|
--set ANSI_NULLS ON--set QUOTED_IDENTIFIER ON--go------ALTER PROC [dbo].[spEventTagCloud]--as--BEGINDECLARE @RECORDCOUNT INT;DECLARE @SearchString varchar(2000);DECLARE @QRY VARCHAR(2000);DECLARE @SE VARCHAR(2000);SELECT @RECORDCOUNT=COUNT(*) FROM TBEVENTSDECLARE @ST INT;SET @ST=1;CREATE TABLE #TEMP2( MYTAGS VARCHAR(2000))--CREATE TABLE #TEMP3--(-- TAGCOUNT INT--)CREATE TABLE #TEMP1( STR1 VARCHAR(2000))WHILE @ST<@RECORDCOUNTBEGIN 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+1declare @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);beginset nocount onif (@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 endelse 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 FOLLOWEVENTTAG_________ASP.NET, C#, VB.NETWELCOME TO ASP.NETASP.NET BOOKS,C#.NET BOOKSI WOULD LIKE TO SELECT ALL COLUMNS FROM MY TABLE SEARCH TAG IS ASP.NETTHE FOLLOWING CODE WILL GENERATE AND SPLIT IT ASTAG ____ASP.NETc#VB.NETWELCOME TO ASP.NETASP.NET BOOKSC#.NET BOOKSHOW TO SEARCH AN EXACT MATCH AS ASP.NET THE ONLY ONE ROW |
|
nr
SQLTeam MVY
12543 Posts |
|
ilayaraja.ajsquare
Starting Member
8 Posts |
Posted - 2008-04-15 : 01:51:22
|
books catalog, education, best booksBirthday, Party GopiSession study, usercontrolSession, studyHolidayTechnical Session, Asp.netPatterns, session, asp.netday, partyevents for Lords, daily thingevents managerevents thingsmeeting, administratormarriageproject ,event, demomadurai ,eventdemo, event calendarrangoli, eventDemo Projectevent project______________the above values are my tags.i want to search "event"it should return only the exact match seperator by comma or by singlenot all the rows.... |
 |
|
|
|
|
|
|