I'm looking for a way to accomplish something kind of like fuzzy search with Oracle. If this has already been answered, I'll gladly accept a link, but I'm so new to Oracle that I'm not even sure how to quickly search for what I want.
Given the following query:
SELECT VEND_CUST_CD, LGL_NM, ALIAS_NM
FROM {{DB_NAME}}.{{DB_TABLE}}
WHERE ({{condition_1}}) AND ({{condition_2}}) AND (upper(LGL_NM) LIKE upper('%{{term}}%')
ORDER BY LGL_NM
What I'd like to get in my response is a particular order. Let's imagine term=ze for the purposes of this.
I'd like to get results ordered like so:
So that what I'm getting back first is words that start with term followed by an alphabetical list of words that contain term within them.
I hope this is clear.
I suppose you can order the results like so:
ORDER BY CASE WHEN LGL_NM LIKE '{{term}}%' THEN 1 ELSE 2 END, LGL_NM