Stored Procedure Maximum Pattern Match

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

Does your suggested solution provide an answer?

What if you data contains:

033541210335412203354123

These will all match 03354%. So which one is best?

Maybe there is no answer to that.

One "fix" is to sort the data in descending order and pick the first result.

Can you do this (sorry, I have no Oracle DB to test with):

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 can't test, you may have to adjust the syntax.