Truncate string field from the right

Hello,
Trying to return the rightmost 5 characters in a multi-length field (1-9 characters).  In Excel or VB I could use the RIGHT function to get what I want, but I'm using Discoverer which is not recognizing RIGHT as a registered function.  RTRIM only weeds out the blanks so that doesn't work.  Any other suggestions?

TIA
mriozzoAsked:
Who is Participating?
 
jdlambert1Commented:
I believe the only string function specific to Discoverer is AllTrim.

However, the INSTR and SUBSTR Oracle PL/SQL string functions should work. For syntax, see: http://www.psoug.org/reference/substr_instr.html
0
 
slightwv (䄆 Netminder) Commented:
It's not pretty but should work (it's the quickest I can come up with off the top of my head).

drop table tab1;

create table tab1( col1 varchar2(9));

insert into tab1 values('1');
insert into tab1 values('12');
insert into tab1 values('123');
insert into tab1 values('1234');
insert into tab1 values('12345');
insert into tab1 values('123456');
insert into tab1 values('1234567');
insert into tab1 values('12345678');
insert into tab1 values('123456789');
commit;

select substr(lpad(col1,14,0),10) from tab1
/
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
another option:
select iif( len(col1) >= 5 ,  substr (  len(col1) - 4 )   , col1)

Cheers
0
 
binghuCommented:
how about

substr(column_name, length(column)-5,5)
0
 
amulya_333Commented:
Hi,
 binghu 's code will give you the result leaving the last char.
Try this,
substring(columnname, len(column)-4,5)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.