?
Solved

Left Justify field in Oracle

Posted on 2016-09-12
6
Medium Priority
?
173 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 1000 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 35

Accepted Solution

by:
johnsone earned 1000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

771 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