Stored Procedure Maximum Pattern Match

Question

This is my stored procedure in Oracle:

 CREATE OR REPLACE PROCEDURE execute_cproc (  callnum IN VARCHAR2
                                            , RESULT  OUT VARCHAR2)
 AS
   vara_val NUMBER;
   varb_val NUMBER;
 BEGIN
   SELECT a_val, b_val
     INTO vara_val, varb_val
     FROM data_table
    WHERE callnum LIKE numberpattern || '%';
 END;

If CALLNUM is 03354123 then I am getting 2 results:

1. 03354123 like 033%

2. 03354123 like 03354%

Both are true so I'm getting 2 results.

How to make procedure find the longest matching only, i.e. 03354123 like 03354%?

Table :

Table Name : DATA_TABLE

Columns:

NumberPattern (varchar2) : 033, 03354

a_val ( integer ) : 1, 2

b_val ( integer ) : 1, 2

Answer 1

You must restructure you query. If you want to get the best or max pattern, do this:

  select  a_val, b_val 
  into vara_val, varb_val 
  from 
      (select NumberPattern , 
              a_val,
               b_val,
               rank() over(order by length(NumberPattern) desc) ranking 
         from DATA_TABLE  
         where CALLNUM  like NumberPattern  || '%' ) 
         where ranking = 1

This will set your variables vara_val and varb_val into values 2, 2 respectively. Or if you like to get the pattern as well, just declare another variable that will hold that value then edit your query to this:

    select  NumberPattern ,a_val, b_val 
    into yournew_variable,vara_val, varb_val 
    from 
      (select NumberPattern , 
              a_val,
               b_val,
               rank() over(order by length(NumberPattern) desc) ranking 
         from DATA_TABLE  
         where CALLNUM  like NumberPattern  || '%' ) 
         where ranking = 1

Here's a brief explanation: I created a subquery that consists of the NumberPattern , a_val, b_val and the rank of the NumberPattern according to its length. This will only rank patterns that exists on the CALLNUM parameter that you have provided. So for example, you got patterns 12345,1234,789 and you passed 123456789 to your CALLNUM parameter, it will only rank patterns 12345 and 1234 since your CALLNUM starts with 12345 and 1234, not with 789. Once it is ranked, I selected the NumberPattern, a_val and b_val from that subquery where the ranking is 1