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?

Actually I solved the problem with Reverse function. I curios is there any function that retrieve data from right hand side? For example : 9 characters from right side of string.. — Mehmet, Aug 09 '10 at 14:55

score 73 · Answer 1 · answered Jan 24 '14 at 20:50

73

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

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

answered Jan 24 '14 at 20:50

Sanjay Kattimani

899
1
8
7

What about select substr('12',-3) from dual; the result would be empty – nokieng May 07 '21 at 02:52
1

@nokieng To avoid such situation, I would pad the left side with some spaces and then trim it. `select trim(substr(' ' || '12', -3)) from dual` – LordCatzorz Jun 02 '21 at 17:45

Bharat · Answer 2 · 2010-08-10T08:27:02.537

18

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

edited Aug 10 '10 at 08:27

answered Aug 09 '10 at 14:29

Bharat

6,828
5
35
56

I dont know the length of PHONE_NUMBERS. Each record has different length. – Mehmet Aug 09 '10 at 14:45
@Jack, Bharat's answer will work regardless of the length of the data. A negative argument for the second parameter of SUBSTR counts from the end of the string. – Jeffrey Kemp Aug 10 '10 at 07:54
@Jeffrey I know it, but length of the string is different for each record, so I have to use length function or something like that for the negative argument – Mehmet Aug 10 '10 at 14:05
@Jack: Can you post your query and the output of that query – Bharat Aug 10 '10 at 14:23
1

ok. I solved. Solution: substr(PHONE_NUMBERS,length(PHONE_NUMBERS)-9,9) – Mehmet Aug 10 '10 at 14:28
3

Shouldn't that be substr(PHONE_NUMBERS,length(PHONE_NUMBERS)-8,9) – TomG May 17 '13 at 09:27

score 10 · Answer 3 · answered Aug 09 '10 at 15:51

10

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.

answered Aug 09 '10 at 15:51

Adam Musch

13,286
2
28
32

As per Adam's answer, the greatest prevents the negative offset being longer than the string - i.e. substr('this string',-9) will give the 9 rightmost characters of the string BUT not if the string is SMALLER than 9 characters. The Greatest of (-9,-6) is -6. The greatest ensures it cannot offset longer than the length of the string. – JulesLt Aug 10 '10 at 10:43
Couldn't edit so a second comment. This is the right answer. The greatest prevents the negative offset being longer than the string - i.e. substr('0123456789',-9) will give the 9 rightmost characters of the string. substr('0123456789',-12) gives NULL. The offset cannot be LONGER than the string. The GREATEST ensures this : GREATEST( -LENGTH('0123456789'),-12) is GREATEST(-10,-12) is -10. It ensures the offset is not longer than the string, which the first answer will not do. The first answer will work so long as string is always longer than X. – JulesLt Aug 10 '10 at 10:50
Jack - the greatest () of two negative numbers is the least negative of the two. In the second of the two examples, greatest (-9, -5) evaluates to -5. – Adam Musch Aug 10 '10 at 14:32

score 8 · Answer 4 · answered Aug 21 '12 at 08:18

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

Why is it logical to return null when substr() goes beyond the boudaries? What if I want it to return the strings with length less than my argument? — Henrique Brisola, Nov 04 '21 at 18:39

score 4 · Answer 5 · edited Apr 11 '14 at 12:45

4

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

edited Apr 11 '14 at 12:45

Jeroen Vannevel

43,651
22
107
170

answered Dec 12 '13 at 19:32

Joe User

41
1

3

Please elaborate on your answer. Add some english for clarification. – hgwhittle Dec 12 '13 at 19:49

Mahendra Mustika Wijaya · Answer 6 · 2014-02-13T06:27:46.797

2

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

edited Feb 13 '14 at 06:27

answered Feb 13 '14 at 04:47

Mahendra Mustika Wijaya

21
2

score 1 · Answer 7 · answered Oct 27 '14 at 02:47

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

score 0 · Answer 8 · edited Aug 09 '18 at 16:34

0

I Had the same problem. This worked for me:

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

edited Aug 09 '18 at 16:34

Phil3992

1,059
6
21
45

answered Dec 05 '14 at 17:16

Clayton

1

score 0 · Answer 9 · edited Feb 09 '21 at 15:33

0

Below solution is perfect for this purpose:

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

edited Feb 09 '21 at 15:33

סטנלי גרונן

2,917
23
46
68

answered Feb 09 '21 at 15:07

Ganesh S

13
2

score -1 · Answer 10 · edited Aug 23 '13 at 21:45

-1

Simplest solution:

substr('299123456',-6,6)

edited Aug 23 '13 at 21:45

Taryn

242,637
56
362
405

answered Aug 23 '13 at 20:47

JoeC

1

get string from right hand side

10 Answers10