Stored procedure Maximum pattern matching

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- 03354123, I get 2 results:

• 03354123 like 033%

• 03354123 like 03354%

Both are true, so I get 2 results.

How to make the procedure find the longest match, 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 the request. If you want to get the best or maximum template, do the following:

  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 the variables vara_val and varb_val to values ​​2, 2, respectively. Or, if you like to get the template, simply declare another variable that will hold that value, and then edit your query:

    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

: , NumberPattern, a_val, b_val NumberPattern . , CALLNUM, . , 12345,1234,789, 123456789 CALLNUM, 12345 1234, CALLNUM 12345 1234, 789. , , NumberPattern, a_val b_val , 1

Answer 2

?

, :

03354121 03354122 03354123

03354%. , ?

, .

"" - .

Answer 3

(, Oracle ):

select a_val, b_val into vara_val, varb_val
  from (
    select 
        a_val, b_val, NumberPattern, row_number() as r
    from 
        DATA_TABLE 
    where 
        CALLNUM LIKE NumberPattern || '%'
    order by NumberPattern  DESC)
where r = 1; 

Since I cannot test, you may have to adjust the syntax.