CODEWITHSUNDEEP
sqloracle-databasecount
WOPR
WOPR

Reputation: 319

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!

Upvotes: 1

Views: 46

Answers (1)

user5683823
user5683823

Reputation: 

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
;

Upvotes: 2

Related Questions