Pl/sql Script to return last two digits of MobileNumber of datatype numeric-CodePudding

Here the basic fields look like :

7045069949,
9449087591

Output be like :

Last two digits of fields:

49,
91

Any suggestion from your side ..

CodePudding user response：

Since it's numeric, you may use remainder of dividing by 100 (modulo);

create table test_t(num_col numeric);

insert into test_t values(7045069949);
insert into test_t values(9449087591);

select num_col, mod(num_col, 100)
   from test_t;

dbfiddle

CodePudding user response：

Use SUBSTR:

SELECT col, SUBSTR(col, -2, LENGTH(col)) AS last_two
FROM yourTable;

You could also use REGEXP_SUBSTR:

SELECT col, REGEXP_SUBSTR(col, '\d{2}$') AS last_two
FROM yourTable;

CodePudding user response：

To me, the simplest option is just substr(value, -2), e.g.

SQL> with test (col) as
  2    (select '7045069949' from dual union all
  3     select '9449087591' from dual
  4    )
  5  select col,
  6         substr(col, -2) result
  7  from test;

COL        RE
---------- --
7045069949 49
9449087591 91

SQL>