Reading from back end of string using SUBSTR 3

[CIMTEET]

SUBSTR(string,<i>,<j>) i is start point joint is length from start point.

I have been reading on this and SUBSTR is supposed read from back of string when the <j> portion is negative as long as the <i> portion is not 0 or negative. My code:

set serveroutput on size 2000
DECLARE
s1 VARCHAR2(2000);
BEGIN
s1:='123456789';
DBMS_OUTPUT.PUT_LINE('name '||substr(s1,1,-3));
END;

The output I get is ::
name

When I expect ::

name 789

I can program around this from the front, I just don't like it when things don't work how they are explained. Am I wrong or have misinterpreted the directions? I don't think I have but it could be a possiblity. Please help.

Thanks

 

[Bloke152]

you dont need the <j> value, just make <i> a negative

eg
select substr('Hello',-1) from dual
will give you 'o'

 

[CIMTEET]

Thanks Bloke!

 

[karluk]

To clarify Bloke152's advice, the minus sign needs to go on the <i> value, but in your specific example you also need the <j> value to get the last three characters of the string.

SQL> select 'name '||substr('123456789', -3, 3) from dual;

'NAME'||
--------
name 789

The parameters of -3 and 3 mean start three characters from the right side of the string (-3) and take a substring three characters long (3).

 

[karluk]

Well, I see I should test things before posting. I thought omitting the <j> value would cause it to default to a substring of length 1, but it actually seems to take the entire end of the string. You only need <j>, I guess, if you don't want all of the characters at the end of the string.

Have a star, Bloke152, for teaching this old dog a new trick.

 

[CIMTEET]

Have a star Karluk for being an old dog that is humble enough to learn a new trick!

 

[Bloke152]

Cheers mate!