CODEWITHSUNDEEP
sqlsql-serverdatabasesearchfull-text-search
tonco
tonco

Reputation: 1311

Custom search in SQL with rank by columns

I would like to implement intelligent search form for my users stored in SQL Server. Columns which I want to use for search are show on screenshot below.

enter image description here

But some columns have higher priority. For example if email or phone match with item in DB it will have higher rank then for example founded item by street and city.

I don't even know where to start.

I need just some hint what to study.

My application run under MVC 5 and for db I'm using SQL Server.

Thank you for any help.

Upvotes: 1

Views: 432

Answers (1)

IVNSTN
IVNSTN

Reputation: 9318

so implement your priority algorithm in sort order:

...
set @email = NullIf(@email, '')
set @city_id = NullIf(@city_id, 0)

SELECT
...
WHERE (@email is NULL or t.email = @email)
  AND (@city_id is NULL or t.city_id = @city_id)
  AND ...
ORDER BY
  CASE
    WHEN @email = u.email THEN 1
    WHEN @city_id = t.city_id THEN 2
    ...
  END
OPTION(RECOMPILE)

recompile - to optimize search (execution plan) for different conditions.

Upvotes: 2

Related Questions