with t as ( select '나이키의 신발' txt from dual union all select '나이키의 모자' from dual union all select '나이스한 신발' from dual union all select '나이스한 모자' from dual ) --글자 그대로 파라메터 받을 때 select txt from t, (select regexp_substr(txt,'[^ ]+',1,level) search, level lv from (select '나이키 신발' txt from dual) connect by level <= regexp_count(txt,' ') + 1) where instr(txt,search) > 0 group by txt order by count(search) desc, max(lv) --프로그램단에서 단어 잘라서 파라메터로 던져줄 때 select txt from t where instr(txt,'나이키') > 0 or instr(txt,'신발') > 0 order by regexp_count(txt,'나이키')||regexp_count(txt,'신발') desc