SQL Server query to look for te best possible match for a search

I have table called PRODUCTDATA which has the following data
I'm querying PRODUCTDATA from UI by using a combination of search string  for example if a user searches  using P1,ET,ML  then the  SQL query should search in all columns for possible combinations of P1,ET,ML in any order then it should fetch 20 as Product Units however if a user searches table using TT,KK,ET , SQL query should search all columns for all possible combinations and find the best possible match like ET is present so should fetch 26 as product Units .
Please suggest a way to handle this
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
It is rather preferred that you post your mock data in script / text format so that it can readily be used by the experts while formulating a solutions. Images don't really help much here.

Furthermore, you should also mention the version of the SQL Server you are using. In absence of one, most expert would assume that it is the latest which you may not be targeting.

In any case, here is script I came up with loads of gymnastics, may be there experts out there would probably give you more concise and elegant solution:

declare @productdata table
   [product1] varchar(5)
  ,[product2] varchar(5)
  ,[product3] varchar(5)
  ,[productunits] varchar(5)

insert into @productdata values
 ('P1', null, null, 10)
, ('P1', 'ET', null, 12)
, ('ET', 'P1', 'ML', 20)
, (null, 'ML', 'P1', 15)
, (null, null, 'P1', 8)
, (null, null, 'ET', 26)

declare @searchTerm varchar(100) = /*'TT,KK,ET' --*/ 'P1,ET,ML'

select top 1 [productunits], sum(iif([product1] = [value], 1, 0)) [matched], sum(iif([product1] is null, 1, 0)) [nullcount]
from (
  select [productunits], [product1]
  from @productdata
  union all
  select [productunits], [product2]
  from @productdata
  union all
  select [productunits], [product3]
  from @productdata
) pd
cross join (select [value] from string_split(@searchTerm, ',')) st
group by [productunits]
having sum(iif([product1] = [value], 1, 0)) != 0
order by 2 desc, 3 desc

Open in new window

ronan_40060Author Commented:
Thanks Nitin for our time and solution however my solution should include only SQL query and should work in SQL Server 2016 also on Oracle 11 g
Nitin SontakkeDeveloperCommented:
It is only SQL Query. Much of it can easily be converted to Oracle, I believe.

Except probably the part where a comma delimited string which needs to be converted to a table. I am almost certain there will be ways to do it in Oracle, (Example: http://www.dba-oracle.com/t_extract_comma_delimited_strings_oracle_sql.html)

Or you mean an Identical query which should work BOTH in MS SQL Server and Oracle?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ronan_40060Author Commented:
yes , the plain SQL query should work in both SQL Server and Oracle like
select Productunits
where 'ML' in (product1, product2, product3) and
      'P1' in (product1, product2, product3) and
      (product1 in ('ML', 'P1') or product1 is null) and
      (product2 in ('ML', 'P1') or product2 is null) and
      (product3 in ('ML', 'P1') or product3 is null) ;

but above query  works in case if ML and P1 is used as a search criteria but it doesn't work in case if ML is NOT present in the table in such case  it should search for either ML or P1 only
Nitin SontakkeDeveloperCommented:
Understood. However, we have to also how exactly is this going get constructed and executed too! What parameters will be sending? Will there be 3 separate values?

May be it is easier to get an output just by executing a given query, but it should fit the actual executing environment, isn't it? Being developer, I always think from that point, which I believe is important. Unless of course it is an interview question, then it is a different story altogether.

So please clarify. Is sql something get constructed in application code dynamically and issued to db? Or what?
ronan_40060Author Commented:
hi Nitin

Thanks for your reply , this query gets created dynamically and gets called from an Java application UI that has search fields , please note that the PRODUCTDATA table wont be a huge , it contains almost 50 rows so performance is not an issue also a user can enter 1 search or 2 search or 3 search criteria , abve example I have given with 3 search criteria but it can also be 2 or 1 search .
Please let me know your thoughts
Scott PletcherSenior DBACommented:
The logic is straightforward enough, but making exactly the same query work in SQL and Oracle is not quite as straightforward, but it works if you're on reasonably recent versions of SQL:

FROM @productdata
WHERE product1 IN ('ML', 'P1')
   OR product2 IN ('ML', 'P1')
   OR product3 IN ('ML', 'P1')
    CASE WHEN product1 IN ('ML', 'P1') THEN 1 ELSE 0 END +
    CASE WHEN product1 IN ('ML', 'P1') THEN 1 ELSE 0 END +
    CASE WHEN product1 IN ('ML', 'P1') THEN 1 ELSE 0 END DESC,
    CASE WHEN product1 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN product2 IS NULL THEN 1 ELSE 0 END +

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ronan_40060Author Commented:
Thank you , I will revert on this , again thank you all for our solution and time
ronan_40060Author Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.