Solved

Oracle SQL Match Exact or Longest Prefix

Posted on 2009-04-01
7
2,464 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 74

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 74

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

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

Expert Comment

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

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

688 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