Score search in SQL Server

Question

I'm working on a "nice search" for my users.

I'm looking for a way to search some items using the terms and scoring the search on the same SQL-Server query.

I've got some references googling it.

One of the best that I've found was: http://techforpassion.blogspot.com.br/2013/03/how-to-split-comma-separated-string-and.html

And I've copied part of this, using my data, as follows:

DECLARE @valueList varchar(255)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(100)

SET @valueList = 'how,does,it,works'

SET @pos = 0
SET @len = 0

WHILE CHARINDEX(',', @valueList, @pos + 1) > 0
BEGIN
    SET @len = CHARINDEX(',', @valueList, @pos+1) - @pos
    SET @value = SUBSTRING(@valueList, @pos, @len)

    SELECT
        @value AS TERMO
        ,IFAQ.nome AS NOME
        ,IFAQ.faqid AS ID
    FROM
        itemsfaq AS IFAQ
    WHERE
        IFAQ.nome LIKE '%' + @value + '%'

    SET @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END

I get the result which is fine, but I don't know how to merge all the data tables in a single table, resulting the score of the item within the name and what else is needed.

The idea was:

Get the terms from the searchbar
Split it
Loop through the words
Know what items are displayed in this word, it gets one point
At the end, a table showing the items searched, ordered by the score.

The score is a way to show the user the relevance of the item in the actual search.

---- EDIT

As follows, the image with the actual result

The expected: All the tables merged, with the score sum. Like:

como, Como funciona o FAQ ; 00000-00000-00000-000; 2;

please show sample data and desired result. at the moment `merging all the data tables in a single table resulting to score` does not make sense — RoMEoMusTDiE, Mar 14 '18 at 20:51
That string splitter is about the absolute worst in terms of performance. There is no need for a loop to do these things. My personal favorite is the one here from Jeff Moden. http://www.sqlservercentral.com/articles/Tally+Table/72993/ There are some other excellent choices here. https://sqlperformance.com/2012/07/t-sql-queries/split-strings Or if you are on 2016+ there is the new STRING_SPLIT built in. — Sean Lange, Mar 14 '18 at 20:54
You will need full text search. https://www.codeproject.com/Articles/1006911/Full-Text-Search-in-SQL — Ole EH Dufour, Mar 14 '18 at 20:54
I have removed the c# and ASP.NET MVC tags since you are only using SQL in your question so it's unrelated — Camilo Terevinto, Mar 14 '18 at 21:20

Ole EH Dufour · Accepted Answer · 2018-03-14T21:07:38.927

0

The problem is that like clauses don't use indexes, slowing down your queries.

Plus : executing T-Sql scripts is time consuming.

You really should look into setting up full text search. Not hard to set up and very powerful.

Besides, for safety reasons, you should use parametrized queries.

edited Mar 14 '18 at 21:07

answered Mar 14 '18 at 21:00

Ole EH Dufour

2,968
4
23
48

`Besides, for safety reasons, you should use parametrized queries.` The code as shown does not appear to have any SQL Injection vulnerabilities. Or am I mistaken? – mjwills Mar 14 '18 at 21:13
Mmm, you're making me doubt now, but see https://stackoverflow.com/a/15579926/4180382 – Ole EH Dufour Mar 14 '18 at 21:26
That link is outside the database (the query is being built into a string and then executed). The OP's code doesn't have that issue. – mjwills Mar 14 '18 at 21:34
@mjwills Why not always use parametrized queries,it avoids this kind of discussions. – Ole EH Dufour Mar 15 '18 at 05:53
Because your concern is misguided. To write that query, in T-SQL (remember that code is SQL code, not C# code), with a parameterised query, would be considerably more complicated. You'd need to build the query in a string and then execute it via `sp_executesql` and pass in the parameters. And to what end? It doesn't provide any benefits. It likely confuses the execution plan caching. You are right that parameterised queries are useful to mitigate SQL Injection risks. **But there is no risk here**. _If you believe there is a risk here, maybe point to a citation about it?_ – mjwills Mar 15 '18 at 06:22
No, it is the code that calls the stored procedure/plain query that needs to be parametrized.We agree that the code from OP cannot live on its own, but needs to be called somewhere. No risk in the snippet itself, you're right about that! I'm taking a beer now! – Ole EH Dufour Mar 15 '18 at 10:03

Score search in SQL Server

1 Answers1