4

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
APC
  • 144,005
  • 19
  • 170
  • 281
VJS
  • 2,891
  • 7
  • 38
  • 70
  • please help. let me know if question is not clear or having any doubt – VJS Oct 21 '15 at 05:23
  • what is 'NumberPattern '? and is 'CALLNUM ' a parameter or a column from 'DATA_TABLE '? – Vance Oct 21 '15 at 05:25
  • Table added in the question. NumberPattern is column name in table and CALLNUM is input in the procedure. – VJS Oct 21 '15 at 05:28
  • does not have much exp in oracle but you can use row_number() like this..select a_val, vara_val,varb_val from ( select a_val, b_val into vara_val, varb_val from DATA_TABLE where CALLNUM LIKE NumberPattern || '%') Z qualify row_number over (partition by varb_val order by varb_val desc )=1; – anwaar_hell Oct 21 '15 at 05:28
  • @anwaar_hell : I want maximum match pattern should return. Example 03354123 like 03354% over 03354123 like 033%. Because 03354% is matching more to 03354123 rather than 033%. – VJS Oct 21 '15 at 05:31
  • What would you like to handle ties? What happens when more than one row has a matching value of the maximum length? – APC Oct 21 '15 at 06:36
  • @APC : I will not be having this type of case. DB entries should not be like this. Its a business use case but i will check / reconfirm it again. Thanks for pointing this out. – VJS Oct 21 '15 at 06:45

3 Answers3

2

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

Vance
  • 897
  • 5
  • 9
  • RANK() will return multiple rows when there's a tie for maximum length. The OP hasn't said what they want to happen in that case. – APC Oct 21 '15 at 06:38
  • @APC : I will have to check my business case when there is a tie for maximum length. – VJS Oct 21 '15 at 06:42
  • @Vance : Query you provided is working for me as of now. Giving me best case. Can you please explain this query to me. – VJS Oct 21 '15 at 06:43
  • but even though there's a tie in maximum length in terms of ranking, the pattern will be different from each other. Say for example, pattern 12345 and 12344 has the same length but different from each other. Assuming that patterns in his table is unique, I believe that this query will do fine sir @APC – Vance Oct 21 '15 at 06:45
  • @Vance : yes, pattern in this table is always unique. can you please explain this query to me. – VJS Oct 21 '15 at 06:47
  • 1
    I saw your other post regarding rank() so here's some additional info :) The line : `rank() over(order by length(NumberPattern) desc)` will rank NumberPattern by it's length in descending order so that the pattern with the most length will always be at rank 1. rank () has helped me a lot in my queries as well – Vance Oct 21 '15 at 07:06
  • @ Vance : Thanks friend – VJS Oct 21 '15 at 07:12
0

Does your suggested solution provide an answer?

What if you data contains:

03354121 03354122 03354123

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.

Rene
  • 10,391
  • 5
  • 33
  • 46
  • Yes. In this case it will return all. But if data contains 033541, 033, 03 then it must match to only 1 ie 033541 ( max pattern match ). Can you please help. – VJS Oct 21 '15 at 05:37
  • Answer is in my reply. Sort in descending order and pick the first result. See also Turophile's answer. – Rene Oct 21 '15 at 05:54
-1

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.

Turophile
  • 3,367
  • 1
  • 13
  • 21