SQLTeam.com | Weblogs | Forums

SQL Fulltextsearch ignores 'x' Character in word


#1

Hi,

i have 2 items in Database described as follow::
Item1: FREUND ANDRÜCKROLLE ERGOWHEEL Ø33MM X 45MM, 2 LEICHTL.-KUGELL.,RECHTS
Item2: SPAX SENKKOPF MAGAZINSCHRAUBE VG 4X45MM, GELB A2L, Z2, 800 ST/PAK

Having following sql syntax:

IF (@SearchExpression1 != '') AND (@SearchExpression2 !='') BEGIN
EXEC (
'SELECT [No_], [Description], [Description 2], [Search Description], [Vendor Item No_]
FROM [dbo].[$Item]
WHERE
CONTAINS(, ''"'+@SearchExpression1+'" AND "'+@SearchExpression2+'*"'')')-- prefix search
END

If i have

SET @SearchExpression1 = N'spax';
SET @SearchExpression2 = N'45';

it doesn't find match
but i i have:

SET @SearchExpression1 = N'freund';
SET @SearchExpression2 = N'45';

it found match data.

So, it seems that when searching it ignores the character x in the spax item (4x45), if i put spaces between the x and the 45 , then match is found...

Why is this so? I thinked first is because of stopword, so iy deleted the stopword 'x' from my Stoplist (my stoplist is one copied from system stoplist).

I Think is because of the use of prefix search? How can i change the sql so it founds too 45 enclosed by other characters

Thanks.


#2

I think this is an unfortunate "feature" of FTS. It cannot accept leading wild cards. If it did, you could set your @SearchExpression2 to N'*45*'. I suspect the reason is that when you have a leading wild card, SQL Server cannot use an index and hence forces a table scan - which defeats the purpose of the indexing.


#3

Hi,
i say the users that they must change searchwords, because 45 is too complex (it can be quantity, part of EAN Number, part of ItemNo, it can be weight or height or width....

Supposing they accept this and now searching by "4x45"

is possible work with thesaurus? So when User searches for "4x45" it will be found too data matches like: "4 x 45", "4x 45", "4 x45"

Thanks