Solved

Oracle SQL Char delimited error

Posted on 2016-09-09
5
30 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
  • 2
  • 2
5 Comments
 
LVL 73

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 73

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 31

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now