Ali Shah
asked on
Help in SQL Full Text Search
Hi guys,
I need to search the address table and have implemented full text search in it. However there is one thing happening. When the building number is a single digit the search doesn't return anything
Doesn't return reslut
But when i replace 9 with with 10 or higher the search returns the result
Returns the Result
It seems that it is taking 0-9 as noise words. But if i disable stoplist my search goes considerably slow on front end
I have attached some sample data herewith
regards
Ali
Data.xlsx
I need to search the address table and have implemented full text search in it. However there is one thing happening. When the building number is a single digit the search doesn't return anything
Doesn't return reslut
DECLARE @GlobalStringFraction NVARCHAR(100)
SET @GlobalStringFraction ='9 l10 0aj'
SET @GlobalStringFraction = REPLACE(@GlobalStringFraction, N',',
N'');
PRINT @GlobalStringFraction
SET @GlobalStringFraction = REPLACE(@GlobalStringFraction, N'WAY',
N'');
PRINT @GlobalStringFraction
SET @GlobalStringFraction = REPLACE(REPLACE(REPLACE(@GlobalStringFraction,
N' ', N'<>'),
N'><', N''), N'<>',
N' ');
PRINT @GlobalStringFraction
SET @GlobalStringFraction = N'"' + REPLACE(@GlobalStringFraction,
N' ', N'" AND "')
+ N'"';
PRINT @GlobalStringFraction
DECLARE @str varchar(5000)
Set @str= (SELECT TOP(1000) Count(*)
FROM AddressSearchString AS t
INNER JOIN CONTAINSTABLE([dbo].AddressSearchString, *, @GlobalStringFraction)
AS k ON t.Id = k.[KEY]);
PRINT @str
PRINT @GlobalStringFraction
But when i replace 9 with with 10 or higher the search returns the result
Returns the Result
DECLARE @GlobalStringFraction NVARCHAR(100)
SET @GlobalStringFraction ='10 l10 0aj'
SET @GlobalStringFraction = REPLACE(@GlobalStringFraction, N',',
N'');
PRINT @GlobalStringFraction
SET @GlobalStringFraction = REPLACE(@GlobalStringFraction, N'WAY',
N'');
PRINT @GlobalStringFraction
SET @GlobalStringFraction = REPLACE(REPLACE(REPLACE(@GlobalStringFraction,
N' ', N'<>'),
N'><', N''), N'<>',
N' ');
PRINT @GlobalStringFraction
SET @GlobalStringFraction = N'"' + REPLACE(@GlobalStringFraction,
N' ', N'" AND "')
+ N'"';
PRINT @GlobalStringFraction
DECLARE @str varchar(5000)
Set @str= (SELECT TOP(1000) Count(*)
FROM AddressSearchString AS t
INNER JOIN CONTAINSTABLE([dbo].AddressSearchString, *, @GlobalStringFraction)
AS k ON t.Id = k.[KEY]);
PRINT @str
PRINT @GlobalStringFraction
It seems that it is taking 0-9 as noise words. But if i disable stoplist my search goes considerably slow on front end
I have attached some sample data herewith
regards
Ali
Data.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for giving me this idea. I have used custom stop list and created it from system stop list. have removed 0-9 words and it doesn't slow down as well.
thanks again
thanks again
ASKER