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-15 : 01:56:43
|
How to search a string from the given values.i want to search a string "Session" from the given column of results..it is separator by comma.i want only 2 results from the given value...if i'm writing as like keyword it will return 4 but i need only the exact match of string.._______________________The Result should beSession,StudyPatterns, session, asp.net_______________________But the Result is coming asSession study, usercontrolSession, studyTechnical Session, Asp.netPatterns, session, asp.net________________________anyone tell the solutionbooks 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 |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-04-15 : 03:05:42
|
Can you post the query you used?TrySelect columns from tablewhere ','+@search+',' like '%,'+col+',%'MadhivananFailing to plan is Planning to fail |
 |
|
ilayaraja.ajsquare
Starting Member
8 Posts |
Posted - 2008-04-15 : 06:07:27
|
MADHAVAN SIR THANK YOU FOR YOUR REPLY..I HAVE FOUND THE RESULT BUT I HOPE I HAVE WROTE TWO MANY QUERY DUMMY TABLE AND SO ON.. BUT THE RESULT HAS BEEN COME CORRECTLY SIR...BY THIS CODING IS THERE ANY LACK OF PERFORMANCE...PLEASE REPLYset ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo--set ANSI_NULLS ON--set QUOTED_IDENTIFIER ON--go----ALTER PROC [dbo].[spEventTagCloudSearch]@MYSEARCH VARCHAR(2000)asBEGINDECLARE @RECORDCOUNT INT;DECLARE @SearchString varchar(2000);DECLARE @QRY VARCHAR(2000);DECLARE @SE VARCHAR(2000);SELECT @RECORDCOUNT=COUNT(*) FROM TBEVENTSDECLARE @ST INT;DECLARE @TEMPEVENTID INT;SET @ST=1;CREATE TABLE #TEMP2( MYTAGS VARCHAR(2000), EVENTID INT)CREATE TABLE #TEMP3( EVENTID INT)CREATE TABLE #TEMP1( STR1 VARCHAR(2000))WHILE @ST<=@RECORDCOUNTBEGIN SET @QRY='SELECT TOP ' +CONVERT(VARCHAR,@ST)+' EVENTTAG FROM TBEVENTS' INSERT INTO #TEMP1 EXEC (@QRY) SELECT @SEARCHSTRING=STR1 FROM #TEMP1 SELECT @TEMPEVENTID =EVENTID FROM TBEVENTS WHERE EVENTTAG=@SEARCHSTRING 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 onif (@MatchType != 2) begin set @SearchString = ' ' + @SearchString + ','; set @i1 = 1; while (@i1 != 0) begin set @i2=charindex(',', @SearchString, @i1+1) 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,@TEMPEVENTID 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) END END SET @QRY= 'SELECT EVENTID FROM #TEMP2 WHERE MYTAGS='''+CONVERT(VARCHAR,@MYSEARCH)+'''' INSERT INTO #TEMP3 EXEC (@QRY) SELECT @RECORDCOUNT=COUNT(*) FROM #TEMP3 SELECT * FROM TBEVENTS A INNER JOIN #TEMP3 B ON A.EVENTID=B.EVENTID DROP TABLE #TEMP1 DROP TABLE #TEMP2 DROP TABLE #TEMP3END |
 |
|
|
|
|
|
|