SKU is not full text indexed when enabling Full Text in Admin - ver 3.8, fresh install

Hi,

A new Nop instance is not creating the full text index for Product.Sku when you enable Full text indexing in Admin.

I am using ver 3.8 on a fresh install and new database.  Here is the store procedure in question; it does not seem to create an index on SKU:

ALTER PROCEDURE [dbo].[FullText_Enable]
AS
BEGIN
  --create catalog
  EXEC('
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE [name] = ''nopCommerceFullTextCatalog'')
    CREATE FULLTEXT CATALOG [nopCommerceFullTextCatalog] AS DEFAULT')
  
  --create indexes
  DECLARE @create_index_text nvarchar(4000)
  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[Product]''))
    CREATE FULLTEXT INDEX ON [Product]([Name], [ShortDescription], [FullDescription])
    KEY INDEX [' + dbo.[nop_getprimarykey_indexname] ('Product') +  '] ON [nopCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)
  
  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[LocalizedProperty]''))
    CREATE FULLTEXT INDEX ON [LocalizedProperty]([LocaleValue])
    KEY INDEX [' + dbo.[nop_getprimarykey_indexname] ('LocalizedProperty') +  '] ON [nopCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)

  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[ProductTag]''))
    CREATE FULLTEXT INDEX ON [ProductTag]([Name])
    KEY INDEX [' + dbo.[nop_getprimarykey_indexname] ('ProductTag') +  '] ON [nopCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)
END

It's not the the bug (moved to other forum). It's by design. Starting version 3.80 we search SKU using the exact search

So how are we supposed to use it?  It throws that error out of the box when Full text is enabled.

I created a fresh instance, went to Admin and enabled Full Text, then I go into the website and enter a keyword in the search box and get the error.

If I add the SKU to the FullText_Enable stored Procedure and go to Admin -> Disable Full Text, then re-enable it, then try the search search -- I get results and no error.

It works fine out of the box. "ProductLoadAllPaged" stored procedure doesn't use full-text indexing when searching in SKU anymore. Maybe, you have this stored procedure customized. Are you using any third-party plugins?

Yes, I bought a Theme from Nop-Templates and it came with a bunch of Plugins...  I guess I'm going to contact them now!

Thanks Andrei!

Is there a way to make sku's full text again because I have customers who if they type in 155L would like to see 155L and 155LR

what would you suggest a new configuration be to allow them to search that easily if sku is exact match?

Thanks :)

I'm still working on 3.6 at the moment, but I would think you can do the following:

1. Add the field to the stored procedure that enables the full text index ([FullText_Enable]); in the line that creates the Full text index, add the SKU field at the end:

...
 --create indexes
  DECLARE @create_index_text nvarchar(4000)
  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[Product]''))
    CREATE FULLTEXT INDEX ON [Product]([Name], [ShortDescription], [FullDescription], [SKU])
    KEY INDEX [' + dbo.[nop_getprimarykey_indexname] ('Product') +  '] ON [nopCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)
...

2. Edit the [ProductLoadAllPaged] stored procedure to include a query to get the SKU from the Full text index:

      --SKU
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[Sku], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[Sku]) > 0 '
    --END SKU Query

3. In Admin, disable full text and then re-enable it