sql - Order by instr in Oracle -

i need order query in particular way according search criteria. have ordering of search working db2 need working oracle.

my query db2 uses locate function.

select   *     <table>    upper(name) "'%<search>%' order locate('<search>', upper(name)),          upper(name) 

this return search of review as:

review, review task, review b task, review

i have tried use instr function in oracle not returning had hoped.

select   *     <table>    upper(name) '%<search>%' order instr('<search>',upper(name)),          name 

any ideas on need order looking for?

you have parameters in instr wrong way round. looking pattern upper(nane) inside search term. following works me:

with sample_data (select 'review' name dual union                      select 'review task' name dual union                      select 'review b task' name dual union                      select 'another review' name dual) select   *     sample_data    upper(name) '%review%' order instr(upper(name), 'review'),          name;  name           -------------- review         review task  review b task  review