mriozzo
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
how about
substr(column_name, length(column)-5,5)
substr(column_name, length(column)-5,5)
Hi,
binghu 's code will give you the result leaving the last char.
Try this,
substring(columnname, len(column)-4,5)
binghu 's code will give you the result leaving the last char.
Try this,
substring(columnname, len(column)-4,5)
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)
/