Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle SQL Char delimited error

Posted on 2016-09-09
5
Medium Priority
?
39 Views
Last Modified: 2016-09-12
I'm trying to add the address; which is a char 30, to my oracle sql delimited query.
The length is supposed to be set to 40.

I'm using the following code, but its only returning a 0.
||lpad(substr(emp.addr1,1,40),40,'')

Can someone help me correct this.

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
  • 2
  • 2
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 41791968
you're padding with a null string ''

either don't use the 3rd parameter of lpad,  or provide a space character   ' '  or chr(32)  as the 3rd parameter
0
 

Author Comment

by:metalteck
ID: 41791986
How would you write that?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 41792044
||lpad(substr(emp.addr1,1,40),40)


or

||lpad(substr(emp.addr1,1,40),40,' ')
0
 

Author Closing Comment

by:metalteck
ID: 41792200
Thanks for all the help.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41794175
If you have a value of '123 Main Street' in addr1 that is data type char(30), selecting it would produce a result of 123 Main Street followed by 15 spaces. Then adding the substr and lpad functions would produce a result of 123 Main Street preceded by 10 spaces and followed by 15 spaces. Is that what you want? Also, taking the substr of a fixed char field with a length of 30 from 1 to 40 simply produces the entire field and is not necessary. If you're trying to make the addr1 appear to be right justified to 40 characters, you should trim it first before applying the lpad function.
0

Featured Post

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

719 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