Oracle: sort the results of a regexp_like expression

Question

I have a table containing the following values:

Org    Role  
----   ---------  
XX     Role2  
XXX    Role3  
XXXX   Role4    
null   RoleDefault

I need a query accepting a parameter that would give me the best match using a regexp_like in the where condition.
For example with :userOrg = XXX
a query like this
select * from table where regex_like(:userOrg,Org)

would return

Role2  
Role3

I would like to get something like this

Role3 1  
Role2 2  

in order to pick XXX as the best match.

We the like operator this is feasible.
But we want to switch to regexp_like to use regular expression.
Is this possible?
Thank you

Answer 1

One thing that comes to mind is UTL_MATCH.JARO_WINKLER_SIMILARITY used with RANK(), but it may not yield the same result you are expecting, say 2 for Role2.

SQL Fiddle

Query:

select org,role,
 rank() OVER ( ORDER BY UTL_MATCH.JARO_WINKLER_SIMILARITY( ORG,'XXX') desc ) as matched
from t

Results:

|    ORG |        ROLE | MATCHED |
|--------|-------------|---------|
|    XXX |       Role3 |       1 |
|   XXXX |       Role4 |       2 |
|     XX |       Role2 |       3 |
| (null) | RoleDefault |       4 |