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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I understood your method. I can modify this and use.
Thank You
Thank You
glad I could help
See attached.
match-prefix.txt
match-prefix.txt
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.
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.
ASKER
Thanks all for the ideas
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