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')

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.


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