get string from right hand side

Question

I am writing a query in Oracle.

I want to get a string from the right hand side but the string length is dynamic.

Ex:

299123456789

I want to get 123456789

substr(PHONE_NUMBERS,-X,Y)

X is different for each record.

I tried this:

substr(PHONE_NUMBERS,-length(PHONE_NUMBERS),Y)

and it didn't work..

How can I write this query?

Thanks.

Answer 1

SQL> select substr('123456',-1,6) from dual;

S
-
6

SQL> select substr('123456',-6,6) from dual;

SUBSTR
------
123456

SQL> select substr('123456',-7,6) from dual;

S
-

If you watch above statements, 3 query gives null value as -7 > length('123456').

So check the length of CONT_PHONE_NUMBERS and PHONE_NUMBERS

Hope this helps you

Answer 2

SQL> select substr('999123456789', greatest (-9, -length('999123456789')), 9) as value from dual;

VALUE
---------
123456789

SQL> select substr('12345', greatest (-9,  -length('12345')), 9) as value from dual;

VALUE
----
12345

The call to greatest (-9, -length(string)) limits the starting offset either 9 characters left of the end or the beginning of the string.