asked on

Trim Function in Oracle

I've tried various attempts to remove all spaces after the last character but nothing changes.

update table
set col1 = replace(col1,' ', NULL);

or

update table
set col1 = trim(col1);

or

update table
set col1 = replace(col1,char32, NULL);

select col1, dump(col1) from table;

col1 dump(col1)
ZZZZZ Typ=96 len=20: 90,90,90,90,90,32,32,32,32,32,32,32,32,32,32,32,32,32,32

Instead of trimming the data ahead of time I was trying the following queries, with various versions with and without TRIM and that doesn't work either. I know I've used TRIM successfully in other versions of Oracle so I can't believe version 11.1.0.7 is any different.

The following queries work with data hardcoded with various number of spaces added.

select col1 from table where col1 = 'ZZZZZ';

select col1 from table where col1 = 'ZZZZZ ';

select col1 from table where col1 = 'ZZZZZ ';

but if I assign a variable name above I don't get back a return. The data type is CHAR but I tried changing it to VARCHAR2 hoping that would help.

schwertner

Different alternatives:

update table
set col1 =TRIM(col1);

update table
set col1 = RTRIM(col1);

update table
set col1 = LTRIM(col1);

update table
set col1 = LTRIM(RTRIM(col1));

ASKER CERTIFIED SOLUTION

talahi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schwertner

TRIM can not work on CHAR by definition.CHAR(10) e.g. means that the length of the column is 10 bytes and there is no possibility to change the length.
There was no indication that you use CHAR data type.

Mark Geerlings

Schwertner is correct. That's why the CHAR datatype should normally not be used in Oracle. CHAR = fixed length (in Oracle at least) and Oracle is optimized for variable length values, so use VARCHAR2 in Oracle unless you know the actual data values will always be a particular length.

talahi

ASKER

"The data type is CHAR but I tried changing it to VARCHAR2 hoping that would help."

I indicated I knew how to use the TRIM function but it wasn't working for my application so I was looking for reasons for why it wasn't working. I indicated the datatype was CHAR. I had tried to change the data from to_varchar2(data) but things only worked when I changed everything in the PLSQL to varchar2 for this column of data. Granted the question could have been better asked.

When I did an

update table
set col1 = trim(col1);

where col1 was varchar2 and queried

select col1, dump(col1) from table;

The result was,

col1 dump(col1)
ZZZZZ Typ=96 len=20: 90,90,90,90,90

Mark Geerlings

The Oracle "TRIM" function will work if your columns and variables are VARCHAR2. If they are the CHAR datatype, nothing will be TRIMmed.

ravibhardwaj

Changing the data type in PL/SQL will not going to help you. You need to change the data type in your table. because if the data type is char and the value to be inserted in that field is less then the length specified then Oracle will append blank spaces to the value.