SQLTeam.com | Weblogs | Forums

Fulltext Search doesn't find String having a minus sign


#1

Hi,

i try a fulltext search

SELECT [No_], [Description], [Description 2], [Search Description], [Vendor Item No_]
FROM [dbo].[DBTESTVERSION 23_08$Item]
WHERE [Fade Out] = 0 AND
CONTAINS(*, '"PALette"' )

this finds all data having "palette", but if i have some data where the description is: "euro-palette", then this row did not appear as result, i think is because of the minus sign in the data. But why is this so? And what can i do for change this, so when i search for "palette" that "euro-palette" will be found too.

Thanks.


#2

refer https://support.microsoft.com/en-us/kb/200043

Dashes '-' Ignored in Search with SQL Full-Text

Execute sys.dm_fts_parser on your strings to get an idea of how the words or data are going to be split and will be stored in the internal index.

select * from sys.dm_fts_parser('"PALette euro-palette"', 1033, NULL, 0)


#3

Hi sqlfresher2k15,

thanks for your answer.

select * from sys.dm_fts_parser('"PALette euro-palette"', 1031, NULL, 0)

i doesn't understand how this can help me to clarify how the words are splitting?

if i run this select i obtain 3 Rows as result with display terms: palette, euro and palette
special term is "exac match", occurence 1,2,3

What did me this say? That if the Searchword is "PAlette euro-palette" then FTS is looking for "palette euro palette" ?

And how can help me this if the searchword is "palette euro palette", and what i'm looking for is "palette euro-platte" with the minus sign? Well , your MS Link recommand use the LIKE instead of CONSTAIN , i will test this and look if is a difference in Performance.

Thanks

EDIT: Is only the minus and plus sign problematic? or are other characters causing problems? If is only plus/minus signs, then i can change the search terms in our DataRows, normaly we copy the fields Description and Description 2 in the search description, i can substring the minus/plus signs before save the search description, then i have in description: "euro-palette", but in Search description "europalette" and then it must found the datarow when searching for "palette"