Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle SQL Match Exact or Longest Prefix

Posted on 2009-04-01
7
Medium Priority
?
2,508 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 2000 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

636 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