Full Text Search

As many of you guys know Nop_Commerce doesn't have Full-Text search ability on the Products Search. This means that when you have a product named as "Nike Barcelona Away Jersey" and you type to search "Barcelona Jersey" it will return nothing!

So...I found that implementing SQL Full-Text search was pretty easy!
I did this on a GoDaddy Windows Shared Hosting, nop 1.9, and I implemented this only on the Product Name column of the  Nop_Product table since in my scenario all searches are mostly based on the product name. You could also do the same to search on the localized product name, short description, specs, etc.

Steps:
1) Connect to your DB with SQL Management Studio
2) Open a new query window
3) Paste and run the following t-SQL code:

USE DatabaseName;
GO
CREATE FULLTEXT INDEX ON dbo.Nop_Product
(Name
  Language 1033)
KEY INDEX Nop_Product_PK ;
GO

Note: Language 1033 is English, search on google for other languages or how to set to default.
4) Now that we have the Full-Text enabled on that column we need to modify the SP Nop_ProductLoadAllPaged

After the line: SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'
Add the following:
        DECLARE @FTS_Keywords nvarchar(400)
  SET @FTS_Keywords = @Keywords
  SET @FTS_Keywords = '"' +  LTRIM(RTRIM(@FTS_Keywords)) + '*"'
  -- This method cleans multiple spaces between words
  SET @FTS_Keywords = REPLACE(@FTS_Keywords, '  ', ' þ')
  SET @FTS_Keywords = REPLACE(@FTS_Keywords, 'þ ', '')
  SET @FTS_Keywords = REPLACE(@FTS_Keywords, 'þ', '')
  --SET SEARCH WILDCARDS
  SET @FTS_Keywords = REPLACE(@FTS_Keywords, ' ', '*" AND "')

...at this point an input search text like 'barcelona away jersey'
is being copied to variable @FTS_Keywords and looks like  ' "barcelona*" AND "away*" AND "jersey*" '
The * is to be able to type 'jersey' and get 'jerseys'
The 'AND' is to only get results that contains all parameters. If I put 'OR' instead of 'AND' when I type 'barcelona jersey' I will get all jerseys + the barcelona jerseys. (don't want that in my case)

5) Finally replace this line:
                    patindex(@Keywords, p.name) > 0
by:
        CONTAINS(p.name, @FTS_Keywords)

6) WOALA! ...now you can search something like 'barcelona away jersey' or 'away jersey barcelona' or 'jersey barcelona away' and you'll get the results you want!

Happy coding!
Damiano

damiano wrote:


Hello Damiano,

Thank you for your code.

When creating the Fulltext Index, I'm getting this error:

A default full-text catalog does not exist in database 'nopCommerce' or user does not have permission to perform this action.

Do you have any idea whats going on?

Thank you.

Are you in a shared hosting database? If so, i guess it relates to user permissions, as the message says.

damiano wrote:


Hi Damiano,

I can't find this exact line of code:  patindex(@Keywords, p.name) > 0 to change with CONTAINS(p.name,@FTS_Keywords)

what I have is:

-- search standard content
patindex(@Keywords, isnull(p.name, '')) > 0
or patindex(@Keywords, isnull(pv.name, '')) > 0
or patindex(@Keywords, isnull(pv.sku , '')) > 0
or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.ShortDescription, '')) > 0)
or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.FullDescription, '')) > 0)
or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pv.Description, '')) > 0)          
-- search language content
or patindex(@Keywords, isnull(pl.name, '')) > 0
or patindex(@Keywords, isnull(pvl.name, '')) > 0
or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pl.ShortDescription, '')) > 0)
or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pl.FullDescription, '')) > 0)
or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pvl.Description, '')) > 0)

Any idea?

I'm working with nop 1.8

Thank you

I forgot to mention that I did it on 1.9. (I've just edited the post to mention that)
In 1.8 your line would be the first one you put:
patindex(@Keywords, isnull(p.name, '')) > 0  (this line refers to product, the second one refers to pv : product variant)

Damiano,

Thankyou so much for this code, works great. Saves me some much time on implenting new search SP.

Blake

Hi Guys,
I have tried to Create the full text index but i get the following error:

CREATE FULLTEXT INDEX ON dbo.Nop_Product
(Name
Language 1033)
KEY INDEX Nop_Product_PK ;
Msg 9967, Level 16, State 1, Line number 1
A default full-text catalog does not exist in database 'db1090557_ukbs' or user does not have permission to perform this action.

I am on shared hosting so the support team gave my user owner permissions and also tried to do it using the SU account and got the same error.

Im using v1.9

Thanks

How to use it with a thesaurus?
Thanks.

What do you mean?

Thanks

I was talking about XML files in C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\FTData\ to specify synonyms for the full text search (see http://msdn.microsoft.com/en-us/library/ms142491.aspx).
After some tests I think it works by using FREETEXT instead of CONTAINS.