October 10, 2011 at 12:27 am
There might be some understanding issue, I have a table with two columns (id and Title). I have applied fulltext search on these columns.
following is data in Title column as:
1:1+LBG
1:1+BBG
1:1+SBG
1:1+LBG+Fp
1:1+BBG+Fp
1:1+SBG+Fp
2:1+SBG+Fp
2:1+LBG+Fp
2:1+BBG+Fp
2:1+BBG
2:1+LBG
2:1+SBG
2.35:1+SBG
2.35:1+LBG
2.35:1+BBG
2.35:1+BBG+Fp
2.35:1+LBG+Fp
2.35:1+SBG+Fp
16:9+LBG+Fp
16:9+SBG+Fp
16:9+BBG+Fp
16:9+BBG
16:9+LBG
16:9+SBG
Feb 23 18.34 [Meet Now Mark.Hazelhof]
BigImage+16:9
SmalImage+16:9
LongImage+16:9
BigImage+16:9+FP
SmalImage+16:9+FP
LongImage+16:9+FP
I am trying to search following and the results are NOT as I am looking for:
searching for ':1+' but its showing all titles:
declare @isExactSearch bit , @SearchText varchar(100)
set @SearchText = ':1+'
set @isExactSearch = 0
set @isExactSearch = patindex('"%"', @SearchText)
if @isExactSearch = 1
begin
set @SearchText = '(' + @SearchText + ')'
end
else
begin
set @SearchText = '("' + REPLACE(REPLACE(@SearchText, '"',''), ' ','*") OR ("') + '*")';
end ;
set @SearchText = REPLACE(@SearchText, ':',':''''')
select @SearchText
SELECT [MashupMetaInfo_Id], MashupMetaInfo_Title, MashupMetaInfo_Tag, RANK FROM MashupSearchLookup AS FT_MashupMetaInfo WITH (NOLOCK)
INNER JOIN CONTAINSTABLE(MashupSearchLookup, (MashupMetaInfo_Title, MashupMetaInfo_Tag) , @SearchText ) AS KEY_TBL
ON FT_MashupMetaInfo.ID = KEY_TBL.
order by RANK desc
Searching for '2:1+' is giving me following output:
Feb 23 18.34 [Meet Now Mark.Hazelhof]
2:1+SBG+Fp
2:1+LBG+Fp
2:1+BBG+Fp
2:1+BBG
2:1+LBG
2:1+SBG
I need RANK column as I have to put this in Order by clause and i can't use Like operator because the like operator won't return me RANK column. Several months ago i raised similar issue and did NOT get the ultimate solution. is there anyone who can let me know how can i make a fine tunned search based on criteria OR is there some know issues/limitations that we have to compromise with fulltext search functionality.
Shamshad Ali.
October 10, 2011 at 5:38 am
maybe i'm oversimplifying, but can't you simply wrap the query results with the RANK function ?
SELECT RANK() OVER (ORDER BY [MashupMetaInfo_Id], MashupMetaInfo_Title, MashupMetaInfo_Tag) AsTheRank,
MySubQuery.*
FROM
(
...your original query goes here....
) MySubQuery
ORDER BY
[MashupMetaInfo_Id],
MashupMetaInfo_Title,
MashupMetaInfo_Tag
Lowell
October 10, 2011 at 7:19 am
Looking at your query I am a little confused why you are joining the fulltext search (CONTAINSTABLE) back onto the source table.
Why not just do
SELECT [MashupMetaInfo_Id], MashupMetaInfo_Title, MashupMetaInfo_Tag, RANK
FROM CONTAINSTABLE(MashupSearchLookup, (MashupMetaInfo_Title, MashupMetaInfo_Tag) , @SearchText )
order by RANK desc
EDIT:
Also using OR is problematic will fulltext and is a known bug. Performance is very poor when you need to perform something like where you have an or you should use a UNION. See this article
http://sqlking.wordpress.com/2010/06/06/slow-fulltext-query-performance-using-freetext-and-contains/
MCITP SQL 2005, MCSA SQL 2012
October 11, 2011 at 2:55 am
Full text indexing has some built-in word breaking features that you cannot override. For example, the string 'BigImage+16:9+FP' is broken down into the tokens: bigimage, 16, nn16, 9, nn9, fp. The + character is considered noise and lost entirely. This is standard behaviour of SQL Full Text indexing.
You can see how other individual strings are processed using the sys.dm_fts_parser function. There are other functions that let you see more about the internals of the fulkl text index, BOL can give you details.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply