[oracle] get string from right hand side

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?

I Had the same problem. This worked for me:

 CASE WHEN length(sp.tele_phone_number) = 10 THEN
                   SUBSTR(sp.tele_phone_number,4)

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

Below solution is perfect for this purpose:

select mod('299123456789',1000000000) from dual;

I just found out that regexp_substr() is perfect for this purpose :)

My challenge is picking the right-hand 16 chars from a reference string which theoretically can be everything from 7ish to 250ish chars long. It annoys me that substr( OurReference , -16 ) returns null when length( OurReference ) < 16. (On the other hand, it's kind of logical, too, that Oracle consequently returns null whenever a call to substr() goes beyond a string's boundaries.) However, I can set a regular expression to recognise everything between 1 and 16 of any char right before the end of the string:

regexp_substr( OurReference , '.{1,16}$' )

When it comes to performance issues regarding regular expressions, I can't say which of the GREATER() solution and this one performs best. Anyone test this? Generally I've experienced that regular expressions are quite fast if they're written neat and well (as this one).

Good luck! :)

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.

If you want to list last 3 chars, simplest way is

 select substr('123456',-3) from dual;

substr(PHONE_NUMBERS, length(PHONE_NUMBERS) - 3, 4)

SELECT SUBSTR('299123456789',DECODE(least(LENGTH('299123456789'),9),9,-9,LENGTH('299123456789')*-1)) value from dual  

Gives 123456789

The same statement works even when the number is less than 9 digits:

SELECT SUBSTR('6789',DECODE(least(LENGTH('6789'),9),9,-9,LENGTH('6789')*-1)) value from dual  

Gives 6789

Simplest solution:

substr('299123456',-6,6)

the pattern maybe looks like this :

substr(STRING, ( length(STRING) - (TOTAL_GET_LENGTH - 1) ),TOTAL_GET_LENGTH)

in your case , it will like this :

substr('299123456789', (length('299123456789')-(9 - 1)),9)

substr('299123456789', (12-8),9)

substr('299123456789', 4,9)

the result ? of course '123456789'

the length is dynamic , voila :)