Search Exact match within a column

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

.NET Inside SQL Server (2005)

Search Exact match within a column

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 be
Session,Study
Patterns, session, asp.net
_______________________
But the Result is coming as
Session study, usercontrol
Session, study
Technical Session, Asp.net
Patterns, session, asp.net
________________________
anyone tell the solution

books catalog, education, best books
Birthday, Party Gopi
Session study, usercontrol
Session, study
Holiday
Technical Session, Asp.net
Patterns, session, asp.net
day, party
events for Lords, daily thing
events manager
events things
meeting, administrator
marriage
project ,event, demo
madurai ,event
demo, event calendar
rangoli, event
Demo Project
event project

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-15 : 03:05:42

Can you post the query you used?

Try

Select columns from table
where ','+@search+',' like '%,'+col+',%'

Madhivanan

Failing 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 REPLY

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

--set ANSI_NULLS ON
--set QUOTED_IDENTIFIER ON
--go
--
--
ALTER PROC [dbo].[spEventTagCloudSearch]
@MYSEARCH VARCHAR(2000)
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;
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<=@RECORDCOUNT
BEGIN
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 on
if (@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
end
else
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 #TEMP3
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources