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

User · Answer

If you want to list last 3 chars  simplest way is    select substr  123456  -3  from dual

User · Answer

SQL gt  select substr  999123456789   greatest  -9  -length  999123456789     9  as value from dual   VALUE --------- 123456789  SQL gt  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

User · Answer

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    lt  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

User · Answer

Below solution is perfect for this purpose  select mod  299123456789  1000000000  from dual

User · Answer

substr PHONE NUMBERS  length PHONE NUMBERS  - 3  4

User · Answer

SQL gt  select substr  123456  -1 6  from dual   S - 6  SQL gt  select substr  123456  -6 6  from dual   SUBSTR ------ 123456  SQL gt  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

User · Answer

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

User · Answer

Simplest solution    substr  299123456  -6 6

User · Answer

I Had the same problem  This worked for me     CASE WHEN length sp tele phone number    10 THEN                    SUBSTR sp tele phone number 4

User · Answer

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

[oracle] get string from right hand side

Examples related to oracle

Examples related to plsql