Solved

Oracle SQL Char delimited error

Posted on 2016-09-09
5
35 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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Select question from MySQL 1 20
Search query matching words 20 38
Oracle Errors 11 51
sql update 2 37
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

749 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