[FIXED] Why can't I trim a column of type CHAR?

Issue

Like the title says, if a create a table in my DB :

CREATE TABLE TEST ( FIELD CHAR(20 CHAR) NULL ) NOLOGGING NOCOMPRESS NOCACHE;

Insert this :

Insert into TEST (FIELD) Values ('TEST -here are blank spaces- '); COMMIT;

Then i run the following statement :

UPDATE TEST SET FIELD = TRIM(FIELD); COMMIT;

but the field still has blank spaces, notice that if I change the data type to varchar2, it works ... does anyone know why?

Thanks!

Solution

char is a fixed width data type. A char(20) will always and forever have a length of 20. If you try to insert a shorter string, it will be padded with spaces to the fixed width length of the field. So

UPDATE TEST SET FIELD = TRIM(FIELD); 

removes the spaces due to the trim function, then adds them back because the string that gets written has to be exactly 20 bytes long.

Practically, there is almost never a case to use char. You're almost always better off with a varchar2. varchar2 is a variable length data type so there is no need for the database to append the spaces to the end.

Answered By - Justin Cave

Answer Checked By - Mildred Charles (PHPFixing Admin)