[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle SQL Match Exact or Longest Prefix

Posted on 2009-04-01
7
Medium Priority
?
2,527 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 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
We are witnesses that everyone is saying that our children shouldn't "play" with a technology because it is dangerous. This article is going to prove that they are wrong.
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month18 days, 14 hours left to enroll

834 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