Metalteck
asked on
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
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,
Can someone help me correct this.
Thanks
ASKER
How would you write that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all the help.
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.
either don't use the 3rd parameter of lpad, or provide a space character ' ' or chr(32) as the 3rd parameter