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

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;