Oracle SQL Match Exact or Longest Prefix

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.
LVL 8
shanikawmAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
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
 
sdstuberCommented:
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
 
shanikawmAuthor Commented:
I understood your method. I can modify this and use.
Thank You
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
glad I could help
0
 
awking00Commented:
See attached.
match-prefix.txt
0
 
sdstuberCommented:
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
 
shanikawmAuthor Commented:
Thanks all for the ideas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.