Social Icons

Functions Part2

TeradataWiki-Teradata Functions
To remove a particular series of leading or trailing characters you use the TRIM function.
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.

No comments:

Post a Comment