Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10479
  • Last Modified:

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
0
mriozzo
Asked:
mriozzo
2 Solutions
 
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
 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now