By default it removes spaces, and only at the beginning or end of a value.
You can trim BOTH the leading and trailing spaces (or a different character) by using the BOTH key word,
When this used on numeric data values, the database will perform an implicit CAST on the numeric value (to make it character) prior to trimming it.
Below example will give you before and after results.
SELECT Last_Name||', '||First_Name
FROM Employee WHERE Employee_Number = 1001;
((last_name||', ')||first_name)
----------------------------------------------------
Hoover , William
Last_Name is defined as CHAR(20).
SELECT TRIM(Last_Name)||', '||First_Name
FROM Employee WHERE Employee_Number = 1001;
((Trim(BOTH FROM last_name)||', ')||first_name)
------------------------------------------------------------
Hoover, William
Trimming Other Than Space
How can trim something other than spaces. Only a single character may be specified for trimming.
The below two requests return the same result.
SELECT TRIM(TRAILING FROM 'abc ') || 'XYZ';
SELECT TRIM(TRAILING ' ' FROM 'abc ') || 'XYZ';
(Trim(TRAILING ' ' FROM 'abc ')||'XYZ')
-----------------------------------------------
abcXYZ
Here we trim trailing underscores from a field.
SELECT 'abc_______', TRIM(TRAILING '_' FROM 'abc_______');
'abc_______' Trim(TRAILING '_' FROM 'abc_______')
------------ ------------------------------------
abc_______ abc
Trimming to get the number of non-space characters.
SELECT CHARACTER_LENGTH('ABC '), CHARACTER_LENGTH(TRIM('ABC '));
Characters('ABC ') Characters(Trim(BOTH FROM 'ABC '))
-------------------- -------------------------------------------------------
6 3
Trimming Numbers
When using TRIM on numeric fields, the database performs an implicit CAST to character prior to doing the trim.
SELECT -999, TRIM(-999);
-999 Trim(BOTH FROM -999)
------ --------------------------------
-999 -999
Note the alignment of the trimmed result. Left alignment indicates character data.
