• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2059
  • Last Modified:

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.

0
talahi
Asked:
talahi
  • 2
  • 2
  • 2
  • +1
1 Solution
 
schwertnerCommented:
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));

0
 
talahiAuthor Commented:
Tried all, comiitted the data and all remains the same.

I changed the datatype from CHAR to VARCHAR2 and that worked.  I guess TRIM and datatype CHAR don't work together.
0
 
schwertnerCommented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
talahiAuthor Commented:
"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





0
 
Mark GeerlingsDatabase AdministratorCommented:
The Oracle "TRIM" function will work if your columns and variables are VARCHAR2.  If they are the CHAR datatype, nothing will be TRIMmed.
0
 
ravibhardwajCommented:
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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now