CODEWITHSUNDEEP
sqloracle-databaseplsql
Mahe
Mahe

Reputation: 1

In a oracle procedure to get the maximum number from Varchar data type field having a prefix

I have a table called ORG, In which I have a field called ORG_ID, I need to get the maximum value from the ORG ID for a specific prefix. For example

Table ORG

ORG_ID ORG_NAME
AB1 Apple
AB2 Google
HQ1 Microsoft
AB3 Tesla
HQ2 Amazon

Now, I want to get the maximum numeric value associated with the prefix AB, I am using below

select max(ORG.ORG_ID)
from ORG
where ORG_ID like 'AB'

It returns AB3 I can use regular expression to get the numeric value 3. My concern is does the above select query always guarantee the max numeric value with the sequence AB.

code

DECLARE
      v_max_number  NUMBER;
       v_max_org_id  VARCHAR2 (20);
       v_max_var     VARCHAR2 (20);
  
       CURSOR curb IS
          SELECT org_id, name
            FROM tableb
          FOR UPDATE;
 
      cbr           curb%ROWTYPE;
      i             NUMBER := 1;
      
   BEGIN
      SELECT MAX (tablea.org_id)
        INTO v_max_org_id
        FROM tablea
       WHERE org_id LIKE 'AB%';
 
      SELECT REGEXP_SUBSTR (v_max_org_id, '\d+') INTO v_max_number FROM DUAL;
 
      SELECT REGEXP_SUBSTR (v_max_org_id, '\D+') INTO v_max_var FROM DUAL;
 
      -- For Loop to write data to Table B
      OPEN curb;
 
      LOOP
         FETCH curb INTO cbr;
 
        EXIT WHEN curb%NOTFOUND;
 
         v_max_org_id := v_max_var || TO_CHAR (v_max_number + i);
 
         UPDATE tableb
            SET org_id = v_max_org_id
          WHERE CURRENT OF curb;
 
         i := i + 1;
      END LOOP;
   END;

Upvotes: 0

Views: 254

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

You asked:

My concern is does the above select query always guarantee the max numeric value with the sequence AB.

The "above query" is

select max(ORG.ORG_ID)
from ORG
where ORG_ID like 'AB'

So, does it return MAX numeric value? I don't think so:

SQL> with test (id) as
  2    (select 'AB5'    from dual union all
  3     select 'AB15'   from dual union all
  4     select 'AB1280' from dual             --> 1280 is MAX (compared to 5 and 15)
  5    )
  6  select max(id)
  7  from test
  8  where id like 'AB%'
  9  /

MAX(ID
------
AB5                   --> this is what your query returns

SQL>

Based on sample data in this (and previous) question you posted, consider

SQL> with test (id) as
  2    (select 'AB5'    from dual union all
  3     select 'AB15'   from dual union all
  4     select 'AB1280' from dual
  5    )
  6  select max(to_number(regexp_substr(id, '\d+$'))) result
  7  from test
  8  where id like 'AB%'
  9  /

    RESULT
----------
      1280

SQL>

Upvotes: 1

Related Questions