Metalteck
asked on
Left Justify field in Oracle
I'm using the following code to gather the information.
||rpad(substr(emp.addr1,1, 40),40)
Problem is when I export it, the data comes like this.
How can I get it so that its Left justified with no spaces?
10211 Anywhere Street
Thanks
||rpad(substr(emp.addr1,1,
Problem is when I export it, the data comes like this.
How can I get it so that its Left justified with no spaces?
10211 Anywhere Street
Thanks
If there are leading spaces in the database field and you don't want those to count in your substring length, the the trim would have to go first.
||rpad(substr(ltrim(emp.ad dr1),1,40) ,40)
||rpad(substr(ltrim(emp.ad
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Good point, johnsone. From a related question emp.addr1 was indicated as a char of fixed length 30. The 10211 Anywhere Street example would be those 21 characters followed by nine spaces. So I think all that should be needed is simply ||rpad(emp.addr1,40) to make it left-justified.
The LTRIM is needed. As requested you need to get rid of leading spaces. TRIM isn't needed because you are padding on the right anyway, so there is no need to waste the cycles to remove the trailing spaces (you are just going to add them back anyway). At least the sample data in the original question has leading spaces.
you can add a TRIM call to make sure you remove any leading or trailing spaces that are already in the data
trim(substr(emp.addr1,1,40