WOPR WOPR - 1 month ago 14
SQL Question

Counting matches in Oracle

I'm writing a basic search engine going against an internal data warehouse.

Suppose I have a 'skills' table, like this:

EmpID Skills
----- --------------------
1 ,Java,
2 ,Java,,C#,
3 ,C#,,Ruby,
4 ,Java,,C#,,Python,
5 ,Python,,C#,


I need to write a query that searches the skills table, looking for matches.

If I'm searching for Java AND C#, I would like to see these results:

EmpID Skills Matches
----- ----------------- -------
2 ,Java,,C#, 2
4 ,Java,,C#,,Python, 2


If I'm searching for Java OR C#, I would like to see these results, ordered by Matches:

EmpID Skills Matches
----- ------------------ -------
2 ,Java,,C#, 2
4 ,Java,,C#,,Python, 2
1 ,Java, 1
3 ,C#,,Ruby, 1
5 ,Python,,C#, 1


How could I write that query in SQL (Oracle 11)?

Thanks!

Answer
with
     test_data ( empid, skills ) as (
       select '1', ',Java,'             from dual union all
       select '2', ',Java,,C#,'         from dual union all
       select '3', ',C#,,Ruby,'         from dual union all
       select '4', ',Java,,C#,,Python,' from dual union all
       select '5', ',Python,,C#,'       from dual
     )
-- end of test data; SOLUTION BEGINS BELOW
select empid, skills, 
       case when skills like '%,Java,%' then 1 else 0 end +
       case when skills like '%,C#,%'   then 1 else 0 end as matches
from test_data
order by matches desc, empid   --  ORDER BY is optional
; 
Comments