Re: TRIM and database inserts/update

Dereck L. Dietz wrote:
> In a previous series of posts it was mentioned that using TRIM during
> database inserts basically doesn't trim anything.
>
> If you have 'ABC' it doesn't trim anything and if you have 'ABC ' it
> doesn't trim the two spaces in the string because it's within the quotes.
>
> Someone brought up a question to me about whether a statement like below:
>
> UPDATE table
> SET field_a = trim(field_a);
>
> Where field_a is originally 'ABC ' would result in the field being 'ABC'
> after the update or still 'ABC '.
>
> Thanks for any answers.

I recall that discussion. The question had to do with the VARCHAR2 column type, whether that column type automatically trims trailing spaces, or if TRIM( ) is required to remove the trailing (and initial) spaces. The person had a book about Oracle that implied that the CHAR column type automatically right-pads the data in the column with spaces, and VARCHAR2 does not - that trailing spaces are not added.

If you update a VARCHAR2(5) column to 'ABC ', the value will remain as 'ABC '. If you update a CHAR(5) column to 'ABC ', the value will remain as 'ABC '.

If you then trim the VARCHAR(5) column, the value will become 'ABC'. If you trim the CHAR(5) column, the value will remain 'ABC ', since Oracle will automatically right-pad the column to the full 5 characters.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Aug 13 2006 - 20:19:44 CDT