CODEWITHSUNDEEP
sqloracle-databaseselectsql-order-bysql-like
dgo
dgo

Reputation: 3937

Oracle DB Query Custom Order by

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.

Upvotes: 0

Views: 47

Answers (2)

Iustin Beceneagă
Iustin Beceneagă

Reputation: 153

You can use union all like this:

select * from (
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}}%')

union all

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%}}') AND (upper(LGL_NM) not LIKE upper('{{term%}}')

) order by ...

or like @Salman A 's way

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272066

I suppose you can order the results like so:

ORDER BY CASE WHEN LGL_NM LIKE '{{term}}%' THEN 1 ELSE 2 END, LGL_NM

Upvotes: 5

Related Questions