How to get REGEXP_LIKE that gets the exact word first, then append all the word related later?

Question

Table Desc:

id   desc
0    ball
1    basketball
2    ballpen
3    a ball
4    ball pen

table Desired output

id    desc  
 0    ball  
 3    a ball  
 4    ball pen  
 2    ballpen  
 1   basketball

Even not the same order. But importantly ill get the exact word first, then followed by all the related word. a ball is still considered exact word so it can be first.

Answer 1

Here are two way to return all the rows with the standalone word "ball" first, followed by those that include "ball" within another word.

First up, if you want to ensure row with only "ball" appears first, build a case expression in your sort.

In this check if the value equals your search variable. If it does map to a number such as one. If it doesn't map to a higher number, e.g. two.

To return all the rows that include ball as it's own word, here's two ways:

regexp_like

You can use the regular expression:

(^|\s|\W)ball($|\s|\W)

Which in SQL looks like:

create table t as 
  select 0 id, 'ball' val from dual union
  select 1, 'basketball' from dual union
  select 2, 'ballpen'    from dual union
  select 3, 'a ball'     from dual union
  select 4, 'ball pen'   from dual union
  select 5, 'littlefoot' from dual;

var v varchar2(10);

exec :v := 'ball';

select * 
from   t
where  val like '%' || :v || '%'
order  by case 
  when val = :v then 1
  when regexp_like ( val, '(^|\s|\W)' || :v || '($|\s|\W)' ) then 2
  else 3
end, id;

ID   VAL          
   0 ball         
   3 a ball       
   4 ball pen     
   1 basketball   
   2 ballpen 

Oracle Text

If you create an Oracle Text index on the column, you can use contains to find matching rows. Call this twice. Once to find exact matches. Then again to show matches with a word.

Then sort by the score of these descending. The exact score first, then the other:

create index test_i on t ( val ) indextype is ctxsys.context;

select t.*, score (1), score (2) from t
where  contains ( val, :v, 1 ) > 0
or     contains ( val, '%' || :v || '%', 2 ) > 0
order  by case 
  when val = :v then 1
  else 2
end, score ( 1 ) desc, score ( 2 ) desc;

ID   VAL          SCORE(1)   SCORE(2)   
   0 ball                  4          4 
   3 a ball                4          4 
   4 ball pen              4          4 
   1 basketball            0          4 
   2 ballpen               0          4