Dash in Product SKU with Full Text Search

I am using the Full Text Index in my Db and edited the corresponding stored procedures to make this work, however I found that when my SKU contains a dash, the search will not find them.

I documented my changes in the following post: https://www.nopcommerce.com/boards/t/27958/full-text-search-does-not-pick-up-meta-keywords.aspx

For example, if I search for YX723-S, I do not get any results but if I search for YX723, then I get results.  This is a design "feature" of SQL, per https://support.microsoft.com/en-us/kb/200043

In the article MS recommends to use the "LIKE" clause instead of the CONTAINS...

Now I tried the following change but it throws an error in NOP, although the query runs fine and works fine in SQL Management Studio:

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

Any help will be greatly appreciated!

Hi Chris,

Please have a look at the latest version of nopCommerce (3.80). This behavior was changed. Now we use exact match (not full-text) to search by SKU

Thanks Andrei, I just tested in 3.8 and it seems to work fine however I cannot update to 3.8 just yet and in the meantime I need my search to work.

Can you tell me how to change the query?  I am on 3.6.

The weird thing is when I run the query (with my changes) in SQL Management studio, I do not get any errors  and I get the results I expect.  It's in Nop that I get the error.

chris.navarro7 wrote:
Just download both versions 3.60 and 3.80. And then compare how "ProductLoadAllPaged" stored procedures are implemenented in both versions (see \App_Data\Install\SqlServer.StoredProcedures.sql). Apply the changes related to "SKU" column

The change in the query looks very simple in the 3.8 SQL script, it just compares the SKU with the @Keywords variable directly, however it did not work in my 3.6 instance.

I even removed the full text index on the SKU column and it now cannot find any SKU's at all; neither with a dash or without.

I think I'll be better off changing the existing 3.6 query to use LIKE instead of CONTAINS.

Now, I already changed my query to use LIKE and if I run it in SQL Management studio, it returns the results I expect and does not throw any errors at all.  But when I run a search in NopCommerce, then I get an error.

My current query looks like this now:

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

I tried to remove the % symbols from the query but then I don't get partial matches in my results.

Any help will be very much appreciated.