sql loadallpages doesn't find partial words - how to fix?

2 个月 前
Hi guys,

as i learned theses days loadallpaged procedure does all product search for nop. It is working with MS "Contains". That means we don't get hits for sample product "TestBrand TestMake 120" if we type in "TestBrand Make" or "TestMake" or "Brand Testmake 120". This is kind of annoying for users. So i would like to replace
@sql = @sql + 'OR CONTAINS(p.[Name], @Keywords) '
with something like
@sql = @sql + 'AND p.[Name] 1 like '%' +  @Splitkeypart1 +'% AND p.[Name] 1 like '%' +  @Splitkeypart2+'%' and so on ...

Has anyone successfully done this modification for loadallpaged procedure or anything with similar effect?

All hints welcome!
2 个月 前
Are you using SQL Server full-text search?
2 个月 前
Yes i do.
2 个月 前
If you use like '%...%' rather than Contains(), then it won't use Full-Text search or any indexes.  Your performance might suffer (if you have many products).

See if this helps.
https://stackoverflow.com/questions/4194485/full-text-search-vs-like#:~:text=While%20LIKE%20works%20on%20a,upon%20whole%20words%2Fterms%20only.&text=SQL%20Server%20fulltext%20does%20support,with%20the%20prefix%20term%20operator.

(Alternately, you may be able to use Tags with your split words)
2 个月 前
Hello,

Hm. Hitting "Abcdoor" when typing in "door" is basic search engine stuff imho.
Product tags have no effect on search output.

Did i get that right (@noptools): Suggestion is to add an - OR - Statement to loadallpages procedure which uses a "Contains[@Keywords, ...] backward" statement to fix this?

Thanks for sharing this.
2 个月 前
RE: 'Hitting "Abcdoor" when typing in "door" is basic search engine stuff imho.
Contains/Full-text search is based on words/phrases  (versus LIKE which uses 'pattern')

RE: "Product tags have no effect on search output."
@SearchProductTags  is a param to the stored procedure.  I don't recall how/when it's used.  (You can look at the source code ;)

RE: Suggestion is to add an - OR - Statement...
Yes, the idea is to use both Contains/Full-text and the LIKE (pattern).
It will impact your performance if you have many products.