Find Top 1 best matching string in SQL server

Question

I have a table 'MyTable' which has some business logics. This table has a column called Expression which has a string built using other columns. My query is

Select Value from MyTable where @Parameters_Built like Expression

The variable @Parameters_Built is built from Input parameters by Concatenating all together.

In my current scenario,

@Parameteres_Built='1|2|Computer IT/Game Design &amp; Dev (BS)|0|1011A|1|0|'

Below are the expressions
---------------------
%%|%%|%%|0|%%|%%|0|
---------------------
1|2|%%|0|%%|%%|0|
---------------------
1|%%|%%|0|%%|%%|0|
---------------------

So my above query returns true for all the three rows. But It should return only the second row (Maximum match).

I just don't need a solution with fix for this scenario. It's just a example. I need a solution like choosing the best match. Any idea?

So you mean it should return the row that uses the least number of % variables i.e. the quality of the match? — Tomas McGuinness, Apr 30 '13 at 07:15
@tomasmcguinness Wow, yeah. Good idea. Do you have Query to achieve this? — Maximus, Apr 30 '13 at 07:18
I'm not sure it's possible. Can you not break the | delimited string and store the various values in their own database columns? — Tomas McGuinness, Apr 30 '13 at 07:18
it is possible, it's only a matter whatever it can be done simply or not :) — Nenad Zivkovic, Apr 30 '13 at 07:22
Actually this value is built from the separate columns. My idea is, we can replace % with empty string and find the length of the string. — Maximus, Apr 30 '13 at 07:23

score 6 · Accepted Answer · answered Apr 30 '13 at 07:22

6

Try:

Select top 1 * from MyTable 
where @Parameters_Built like Expression
order by len(Expression)-len(replace(Expression,'%',''))

- this orders the results by the number of non-% characters in expression.

SQLFiddle here.

answered Apr 30 '13 at 07:22

@Mark - Great, it worked thanks. Also thanks to tomasmcguinness for initiating the idea. – Maximus Apr 30 '13 at 07:28

Find Top 1 best matching string in SQL server

1 Answers1