Link to home
Start Free TrialLog in
Avatar of Metalteck
MetalteckFlag for United States of America

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
Avatar of Sean Stuber
Sean Stuber

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
Avatar of Metalteck

ASKER

How would you write that?
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.