Calculating score with multiple matches

Question

I'm trying to create a stored procedure which does a search and calculates according to the matches a score.

DECLARE @Search VARCHAR(MAX) = 'John Doe Washington';

SELECT
[address].Name,
[address].Street,
[address].City,
[address].ZipCode,
[address].Country,
IIF([address].[Name] LIKE '%'+@Search+'%', 1000, 0)
 + IIF([address].[Street] LIKE '%'+@Search+'%', 800, 0)
  + IIF([address].[City] LIKE '%'+@Search+'%', 750, 0)
   + IIF([address].[ZipCode] LIKE '%'+@Search+'%', 300, 0)
    + IIF([address].[Country] LIKE '%'+@Search+'%', 250, 0) AS [Score]
FROM [dbo].[Addresses] AS [address];

Well this works, if I just use e.g. "John Doe" as the column Name may contain it.

But I want to split the @Search parameter later by comma, which works with a function [dbo].[splitstring](@stringToSplit VARCHAR(MAX)) which I found here. The function splits by comma and creates multiple rows.

So now I want to change my query, to make multiple search words work. What I would want is a LIKE IN(SELECT '%'+Value+'%' FROM dbo.splitstring(@Search)) which doesn't exist in MS SQL Server unfortunately.

Also if multiple words matches the column, the score should be higher. That means e.g. the column contains "John Doe", the first word "John" matches with 1000 points, the second word "Doe" matches with an additional 1000 points. The third one, "Washington" doesn't match, so there will be 0 points for this one.

Is there a way to do this or is this not possible at the moment in MS SQL Server?

score 1 · Accepted Answer · answered Aug 10 '18 at 06:01

1

Rather than doing a LIKE IN (select... do a join to it instead

cross join (select '%'+Value+'%' as val FROM dbo.splitstring(@Search)) as words

and then you can sum the scores for all rows

sum(IIF([address].[Name] LIKE val, 1000, 0)
 + IIF([address].[Street] LIKE val, 800, 0)
  + IIF([address].[City] LIKE val, 750, 0)
   + IIF([address].[ZipCode] LIKE val, 300, 0)
    + IIF([address].[Country] LIKE val, 250, 0)
) AS [Score]

Oh, and change the delimiter in your function from a comma to a space.

answered Aug 10 '18 at 06:01

TomC

2,759
1
7
16

Thanks! This works perfectly, produces multiple rows per match, but that's not a problem. Also the ```@Search``` parameter will use commas instead of spaces, I'll trim the string and replace spaces.....Now I'll need to look up what a cross join is, as I've never used it yet :-) – Stefan Schmid Aug 10 '18 at 06:09

score 0 · Answer 2 · answered Aug 10 '18 at 06:01

take XML with i/p of procedure. Please refer below sample.

<data>
    <Name>John Doe</Name>
    <Street>Cross Road</Street>
    <City>Washington</City>
    <ZipCode>188255</ZipCode>
    <Country>US</Country>
</data>

Insert this data into the temp table with column name of Name, Street, City...and apply with your table on column to column.

Michał Turczyn · Answer 3 · 2018-08-10T06:38:10.877

Using WHERE myColumn LIKE '%word%' is overkill, because it is exactly the same as WHERE SUBSTRING('word', myColumn) > 0. So your reuqested condition:

WHERE myColumn LIKE IN(SELECT '%'+Value+'%' FROM dbo.splitstring(@Search))

becomes possible and easy:

WHERE EXISTS(SELECT 1 FROM dbo.splitstring(@Search)
             WHERE SUBSTRING([Value], myColumn) > 0)

Also, you may need to add table alias into a subquery myTable.myColumn.

score 0 · Answer 4 · answered Aug 10 '18 at 06:23

Alternatively, you can omit custom function and use one query (intentionally written in steps):

DECLARE @terms nvarchar(MAX) = N'John,Doe,Wood';

WITH SourceTable AS --demo data, skip, use your Addresses table instead
(
  SELECT * FROM (VALUES
  ('John Doe', 'Street'),
  ('John Cooper', 'Wood')) T(Name,Street)
), Query AS --convert to xml
(
  SELECT CAST('<a>'+REPLACE(@terms,',','</a><a>')+'</a>' as xml) Query
), SimpleTerms AS --split to rows
(
    SELECT Term.value('.', 'nvarchar(MAX)') Term
    FROM Query
    CROSS APPLY Query.nodes('a') AS T(Term)
)
SELECT Name,Street,
  SUM(IIF(Name LIKE '%'+Term+'%',1000,0)) +
  SUM(IIF(Street LIKE '%'+Term+'%',100,0)) Score
  --other ingredients
FROM SourceTable
CROSS APPLY SimpleTerms
GROUP BY Name,Street

Calculating score with multiple matches

4 Answers4