Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

Oracle SQL Char delimited error

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
metalteck
Asked:
metalteck
  • 2
  • 2
1 Solution
 
sdstuberCommented:
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
 
metalteckAuthor Commented:
How would you write that?
0
 
sdstuberCommented:
||lpad(substr(emp.addr1,1,40),40)


or

||lpad(substr(emp.addr1,1,40),40,' ')
0
 
metalteckAuthor Commented:
Thanks for all the help.
0
 
awking00Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now