Sounds like what you need is Oracle Text!
If you create a text index on the name, you can use it to do fuzzy searching. For example:
create table t (
username varchar2(20)
);
insert into t values ('Chris R Saxon');
insert into t values ('Christopher J Sexton');
insert into t values ('Christina Sascon');
insert into t values ('Arnold J Rimmer');
insert into t values ('Dave Lister');
commit;
create index text_i on t ( username )
indextype is ctxsys.context
order by username
parameters ('sync (on commit)')
/
select score(1), t.* from t
where contains(username, 'fuzzy(Chris, 40, 100, w)', 1 ) > 0
order by score(1) desc;
SCORE(1) USERNAME
---------- --------------------
42 Chris R Saxon
10 Christina Sascon
4 Christopher J Sexton
select score(1), t.* from t
where contains(username, 'fuzzy(Saxon, 40, 100, w)', 1 ) > 0
order by score(1) desc;
SCORE(1) USERNAME
---------- --------------------
42 Chris R Saxon
24 Christina Sascon
19 Christopher J Sexton
select score(1), t.* from t
where contains(username, 'fuzzy(J, 40, 100, w)', 1 ) > 0
order by score(1) desc;
SCORE(1) USERNAME
---------- --------------------
42 Arnold J Rimmer
42 Christopher J Sexton
Load the table up with data and the database can still find the what you're looking for quickly:
insert into t
with rws as (
select rownum x from dual connect by level <= 100000
)
select initcap(dbms_random.string('l', 5) || ' ' ||
dbms_random.string('u', 1) || ' ' ||
dbms_random.string('u', 8))
from rws;
commit;
select * from (
select score(1), t.* from t
where contains(username, 'fuzzy(Chris, 40, 100, w)', 1 ) > 0
order by score(1) desc
)
where rownum <= 5;
SCORE(1) USERNAME
---------- --------------------
100 Chris R Saxon
71 Chjrs F Qdljlkvx
63 Chsti Q Lqjefwpy
48 Igqlv Z Chredsiv
48 Chmys H Zwywezht
Elapsed: 00:00:00.279
select * from (
select score(1), t.* from t
where contains(username, 'fuzzy(Saxon, 40, 100, w)', 1 ) > 0
order by score(1) desc
)
where rownum <= 5;
SCORE(1) USERNAME
---------- --------------------
100 Chris R Saxon
90 Soxon B Qhwsdkpd
83 Sabon J Yaiauupa
75 Christina Sascon
71 Saojn B Mkbqkibc
Elapsed: 00:00:00.274
select * from (
select score(1), t.* from t
where contains(username, 'fuzzy(J, 40, 100, w)', 1 ) > 0
order by score(1) desc
)
where rownum <= 5;
SCORE(1) USERNAME
---------- --------------------
72 Christopher J Sexton
72 Arnold J Rimmer
72 Uvkxc J Ghcltdnq
72 Yrwtn J Yytzfzuh
72 Dnvjb J Clpvgpxu
Elapsed: 00:00:00.261
For more details on how fuzzy searching works, see:
https://docs.oracle.com/cd/E11882_01/text.112/e24436/cqoper.htm#CCREF0307