0

Given a table like this:

Product

Column/Field Description Type
Id Unique identifier
Name A short name string
Description Long description string
Tags A string of keywords separated by commas string

How do I perform a search in SQL Server using ASP.Net Core 3.1 MVC or above that returns results in the order of most matches?

The user will enter one or more search keywords in the UI. The fields to be searched to test if any of the keywords match are Name, Description and Tags.

Searching will be case-insensitive. The Description field is a free form text string field, so some words may have pre or post punctuation marks.

And I need the results returned in order of “best match first”, which is defined as how many of the columns/fields in a given row contain the search keywords.

For example, if there were only 3 records in the Product table like this: -

Id Name Description Tags
1 A phone iPhone 4 Mobile Phone
2 iPhone iPhone 5 iPhone mobile phone
3 Mobile Cell phone iPhone

If a user entered “iPhone” and that occurs in all three fields, this should be top of the list. So the results should look like this:

Id Name Description Tags
2 iPhone iPhone 5 iPhone mobile phone
1 A phone iPhone 4 Mobile Phone
3 Mobile Cell phone iPhone

That is, with the best matches at the top.

How do I achieve this with SQL Server using ASP.Net Core 3.1 (or above) MVC and C#?

D.Man
  • 179
  • 1
  • 15
  • You need to count the number of matches and order descending. But first you might want to consider this module, which is faster and has more advanced search capability. It might even have this most matches capability built in https://learn.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-ver16 – Nick.Mc Oct 14 '22 at 09:52
  • Full-text search might be a good option. – Charlieface Oct 14 '22 at 10:47
  • How would I do this without using Full-text search How do I count the number of matches? And can both methods be done via Asp.Net Core 3.1/5 via EFCore? – D.Man Oct 14 '22 at 13:40
  • You can try to refer to the [link](https://stackoverflow.com/questions/52672905/how-do-you-do-fulltext-search-with-entity-framework-core). – Yiyi You Oct 17 '22 at 10:05

0 Answers0