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?

Answer 1

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

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

Answer 2

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 3

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.

Answer 4

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! :)

Answer 5

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