• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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
0
metalteck
Asked:
metalteck
  • 3
  • 2
2 Solutions
 
sdstuberCommented:
if you don't want it to be padded with spaces, don't pad it.
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))
0
 
johnsoneSenior Oracle DBACommented:
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.addr1),1,40),40)
0
 
awking00Commented:
Still not sure why you're using substr if emp.addr1 is char(30). Just ||rpad(trim(emp.addr1),40, ' ')
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
johnsoneSenior Oracle DBACommented:
Why are you using TRIM and not LTRIM?  You are removing spaces from the right (if they are there), only to put them back with RPAD.  Seems like wasted cycles to me.
0
 
awking00Commented:
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.
0
 
johnsoneSenior Oracle DBACommented:
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.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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