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

Open in new window



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

Open in new window


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
Ali ShahSQL DeveloperAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
You can adjust the noise word to remove 0 thru 9 but not to include everything else.  That is, you can create a custom noise list.
0
 
Ali ShahSQL DeveloperAuthor Commented:
Thanks! is a custom noise list inherited by system noise list? I mean if i just eliminate some words from custom stop list will other noise words still be there?
0
 
Ali ShahSQL DeveloperAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.