Solved

Oracle SQL Match Exact or Longest Prefix

Posted on 2009-04-01
7
2,376 Views
Last Modified: 2013-11-11
I have following table (Sample)

CATEGORY       PREFIX          EXACT_MATCH
-----------------    ---------         -----------------
1                          '123'              'Y'
2                          '123'              'N'
3                          '1234'            'N'
4                          '12345'          'N'

...........and 1000+ entries ....
0 for NVL

I need a Query/Function to return the category for a given prefix by exact matching (where EXACT_MATCH='Y') or by possible longest prefix match. So if the given prefix is

123 => 1 (i.e. exactly match with category 1)
1237 => 2 (i.e. longest match prefix is category 2)
123458 => 4 (i.e. longest match prefix is category 4)
132 => 0 (No match found)

I think REGEXP will help for this

Thanks.
0
Comment
Question by:shanikawm
  • 4
  • 2
7 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 24045631
SELECT category
  FROM (  SELECT category
            FROM your_table
           WHERE :your_string LIKE prefix || '%' OR prefix IS NULL
        ORDER BY LENGTH(prefix) DESC NULLS LAST,
                 CASE WHEN :your_string = prefix AND exact_match = 'Y' THEN 1 ELSE 2 END,
                 exact_match ASC)
 WHERE ROWNUM = 1
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24045636
or, with regular expressions


SELECT category
  FROM (  SELECT category
            FROM your_table
           WHERE REGEXP_LIKE(:your_string, '^' || prefix) OR prefix IS NULL
        ORDER BY LENGTH(prefix) DESC NULLS LAST,
                 CASE WHEN :your_string = prefix AND exact_match = 'Y' THEN 1 ELSE 2 END,
                 exact_match ASC)
 WHERE ROWNUM = 1
0
 
LVL 8

Author Closing Comment

by:shanikawm
ID: 31565651
I understood your method. I can modify this and use.
Thank You
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 73

Expert Comment

by:sdstuber
ID: 24048618
glad I could help
0
 
LVL 31

Expert Comment

by:awking00
ID: 24049006
See attached.
match-prefix.txt
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24049200
wouldn't it be easier to just wrap one of the above queries in a function?

querying the the same table 2 or 4 times seems excessive when you could just do it once.

of course, the regular expression version won't work prior to 10g
and the other one won't work prior to 8 since you couldn't put order by inside an inline view

your function would work in version 7,
but you could always go with the old-school method
of open cursor, fetch once and close.  using the inner query as the cursor.
0
 
LVL 8

Author Comment

by:shanikawm
ID: 24057231
Thanks all for the ideas
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
select query - oracle 16 82
Regex rule to match two different url 5 38
T-SQL Convert to PL/SQL 23 62
sql query 5 52
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now