Solved

Left Justify field in Oracle

Posted on 2016-09-12
6
131 Views
Last Modified: 2016-09-15
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
Comment
Question by:metalteck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 41795142
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
 
LVL 35

Expert Comment

by:johnsone
ID: 41795251
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 41795894
Still not sure why you're using substr if emp.addr1 is char(30). Just ||rpad(trim(emp.addr1),40, ' ')
0
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 
LVL 35

Accepted Solution

by:
johnsone earned 250 total points
ID: 41795908
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
 
LVL 32

Expert Comment

by:awking00
ID: 41796592
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
 
LVL 35

Expert Comment

by:johnsone
ID: 41796615
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question