oracle sql search by preference

Question

Taking a sample table:

WITH t(val) AS
     ( SELECT 'my Name' FROM dual
     UNION
     SELECT 'my name' FROM dual
     UNION
     SELECT 'my naim' FROM dual
     UNION
     SELECT 'MY kat' FROM dual
     UNION
     select 'my katt' from dual
    )
SELECT * FROM t;

I need an output by preference:

The query should search for the exact match in the table first, If not found then search by lowering the case, and if not found then only search for soundex,. So the final output for something like:

WHERE val = 'my Name'
  OR lower(val) = lower('my Name')
  OR soundex(lower(val)) = soundex(lower('my Name'))

should be:

output
-----
my Name

Thanks in advance.

Answer 1

For large data sets you'd probably want to avoid the unnecessary tests if any of the earlier ones had found a match.

with t as
   ( SELECT 'my Name' as val FROM dual
   UNION
   SELECT 'my name' FROM dual
   UNION
   SELECT 'my naim' FROM dual
   UNION
   SELECT 'MY kat' FROM dual
   UNION
   select 'my katt' from dual
    )
  exact_match as (
    select *
    from   t
    where  val = 'my Name'),
  lower_case_match as (
    select *
    from   t
    where  lower(val) = lower('my Name') and
           not exists (select null from exact_match)),
  soundex_match as (
    select *
    from   t
    where  soundex(val) = soundex('my Name') and
           not exists (select null from lower_case_match) and
           not exists (select null from exact_match))
select * from exact_match
union all
select * from lower_case_match
union all
select * from soundex_match;

Oracle would most likely materialise the result set of the first two search common table expressions in order to make it more efficient for the subsequent expressions to test whether they returned a result. If the first "exact_match" search returns a result then subsequent searches will not be required to execute.

Answer 2

How about mine: ( Thanks to @Thorsten Kettner).

SELECT val FROM(
  SELECT val, DENSE_RANK()OVER(ORDER BY CASE WHEN val = 'my Name' THEN 1
                                             WHEN lower(val) = lower('my Name') THEN 2
                                             WHEN soundex(lower(val)) = soundex(lower('my Name')) THEN 3
                                        END
                               )rnk
  FROM t
  WHERE val = 'my Name'
  OR lower(val) = lower('my Name')
  OR soundex(lower(val)) = soundex(lower('my Name'))
)
WHERE rnk  = 1; 

Another with FIRST , by Oracle particularly for our purpose:

SELECT MAX(val) keep (dense_rank FIRST ORDER BY priority) AS val
FROM
     (SELECT t.* ,
          CASE WHEN val = 'my Name' THEN 1
               WHEN lower(val) = lower('my Name') THEN 2
               WHEN soundex(lower(val)) = soundex(lower('my Name')) THEN 3
          END priority
     FROM t
     WHERE val                = 'my Name'
       OR lower(val)          = lower('my Name')
       OR soundex(lower(val)) = soundex(lower('my Name'))
     );

Answer 3

Probably you need oracle text, more precisely query relaxation: http://docs.oracle.com/cd/B28359_01/text.111/b28303/query.htm#i1007593

You can imitate something similar like:

select
 *
from
 (select *,
   case
      when condition1 then 3
      when condition2 then 2
      when condition3 then 1
   end relevance
  from
    table
  where
    (condition1 or condition2 or condition3)
  )
order by
  relevance desc

Answer 4

Just filter as you describe, then sort by that order, then grab the first record:

WITH t AS
     ( SELECT 'my Name' as val FROM dual
     UNION
     SELECT 'my name' FROM dual
     UNION
     SELECT 'my naim' FROM dual
     UNION
     SELECT 'MY kat' FROM dual
     UNION
     select 'my katt' from dual
    )
SELECT * FROM
(
  SELECT * FROM t
  WHERE val = 'my Name'
  OR lower(val) = lower('my Name')
  OR soundex(lower(val)) = soundex(lower('my Name'))
  order by
    case 
      when val = 'my Name' then 1
      when lower(val) = lower('my Name') then 2
      when soundex(lower(val)) = soundex(lower('my Name')) then 3
    end
)
WHERE ROWNUM = 1;