Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: question about set statement
From the exemple given by Jan, I would say the field should be RTRIM'ed first to remove the trailing spaces.
HTH,
-- Today, Tomorrow, Together The DOC (The Dutch Oracle Company) Xavier. "Markus Stuhlpfarrer" <mstuhlpfarrer_at_hollomey.com> wrote in message news:3B84ED5D.2EE74496_at_hollomey.com...Received on Thu Aug 23 2001 - 07:18:24 CDT
> syntax error
> lpad has 3 parameters for this:
> select lpad(2,3,'0') from dual
> this gives: '002'
>
> lpad(2,0) gives back an empty string (0 length)
> lpad(2,3) adds spaces (' 2')
> lpad(2,3,'0') adds zeroes ('002')
>
>
>
> "jAnO!" wrote:
>
> > Hi all,
> >
> > I have a column in my table that is poluted by users. It is a 3 position
> > varchar column.
> > The values must be 001 002 003 etc. But now i have values like "1 ". I
> > want to convert this data to the right format, and tried this:
> >
> > set ASN_PULS_CODE = TO_CHAR(ASN_PULS_CODE,'000')
> >
> > didn't work..
> >
> > i also tried this:
> >
> > set ASN_PULS_CODE = lpad(ASN_PULS_CODE, 0)
> >
> > both didn't work... can anybody help me out???
> >
> > thanks,
> >
> > Jan
>