varchar vs char

[Darren Reely]

Fernando,

First let me tell you that Oracle does NOT remove trailing spaces be 
default. At least up to version 8.1.7. I just double checked it. Also 
when Powerhouse writes the extra spaces, Oracle is very glad to accept 
them. This has frustrated us at times with our own Powerhouse/Oracle system.

There is no data dictionary option either. Wouldn't that make a nice 
useful 'feature'. Or better yet, be the default. Hands up if you'd like 
the default behavior to be; keep leading/trailing spaces on user entered 
data. Yes, I realize it would be dangerous to change the default now.

To assure your self that your not writing trailing or leading spaces to 
the database, you would have to use TRUNCATE(LJ(item)) syntax on all of 
your input. But copying one item to the next should not display this 
problem if your data is already clean and your not assigning a CHAR type 
to a VARCHAR type. So perhaps your work isn't as extensive as first thought.

Good luck.

Darren


fernando.olmos at hpa.com.au wrote:
> Can someone please explain why this is happening and is it correct?
> 
> Why is it that in PH (and this does not happen in Oracle), a column with 
> a varchar will retain any trailing spaces when data with spaces after 
> the last char is read into it?
> 
> ie:    
>         define a varchar * 10 = "abc   "        ;has 3 spaces after the 
> 'c' letter
>         define b char*20 = "|" + a + "|"
>         report a b
>         go
> 
> This shows ...
> 
>   A           B
> 
>   abc         |abc   |
> 
> Of course if I change the B define to trunc() the A define it works ...
> 
>         define b char*20 = "|" + trunc(a) + "|"
> 
> It won't make any difference if I change the char*20 to varchar*20 either.
> 
> I don't want to have to use trunc(). We have tons of columns we're 
> migrating over to a new PH environment that are all varchars. I don't 
> want to have to specify trunc() on ever item that populates those 
> columns. Is there a setting in PH that tells the dictionary to 
> "naturally" trunc()?
> 
> Thanks again gang.
> 
> /Fernando Olmos/
> *M.I.S.*
> 
> *HPA***
> Direct:  03 9217 5411
> Mobile: 0410 382 857
> Fax:     03 9217 5716
> 
> ___*www.hpa.com.au*_ 
> <file:///H:/Appdata/Microsoft/Signatures/www.hpa.com.au>
> 
> 
> **********************************************************************
> IMPORTANT
> The contents of this e-mail and its attachments are confidential and intended
> solely for the use of the individual or entity to whom they are addressed.  If
> you received this e-mail in error, please notify the HPA Postmaster, postmaster at hpa.com.au,
> then delete  the e-mail.
> This footnote also confirms that this e-mail message has been swept for the
> presence of computer viruses by Ironport. Before opening or using any
> attachments, check them for viruses and defects.
> Our liability is limited to resupplying any affected attachments.
> HPA collects personal information to provide and market our services. For more
> information about use, disclosure and access see our Privacy Policy at
> www.hpa.com.au
> **********************************************************************
>