Social Icons

Functions Part3

TeradataWiki-Teradata FUnctions
CASESPECIFIC
Teradata database is not case sensitive (case specific), there may be times when we would like it to perform in a case sensitive manner. To accomplish this we use the CASESPECIFIC function.

SELECT Last_Name FROM Employee  WHERE Last_Name = 'brown' (CASESPECIFIC);

*** Query completed. No rows found.

SELECT Last_Name FROM Employee  WHERE Last_Name LIKE '%Ra%' (CASESPECIFIC);

last_name     
-------------------
Ratzlaff             
Rabbit               


Note that "Crane" and "Trader" are also employees.

SELECT Last_Name FROM Employee  WHERE POSITION(('Ra' (cs)) IN Last_Name) > 0;

last_name           
--------------------
Ratzlaff              
Rabbit                



EXTRACT function: 
The EXTRACT function can be used to return portions of a date or time value. It can not extract combinations like "year-month' or "month-day",
The result of the EXTRACT function is an integer value that represents that portion you are extracting

EXTRACT ( { YEAR       } FROM date-value )
            MONTH
            DAY
            HOUR
            MINUTE
            SECOND


SELECT EXTRACT(YEAR FROM DATE'2010-12-20' + 30)  AS Yr,
 EXTRACT(MONTH FROM DATE'2010-12-20' - 30) AS Mth,
 EXTRACT(DAY FROM DATE'2010-12-20' + 30)      AS Dy
;

         Yr          Mth           Dy  
-----------  -----------  -----------
       2011           11           19   


SELECT EXTRACT(HOUR FROM TIME'10:20:30')      AS Hr,
 EXTRACT(MINUTE FROM TIME'10:20:30')    AS Mn,
 EXTRACT(SECOND FROM TIME'10:20:30')  AS Scd;


         Hr           Mn          Scd  
-----------  -----------  -----------
         10           20           30     

  

TYPE Function

The TYPE is an attribute function that returns only the data type of a column or expression, i.e. it does not return result information.

SELECT TYPE(CHARACTER_LENGTH('Indiana Jones')); --> INTEGER
SELECT TYPE(-99999);  --> INTEGER
SELECT TYPE(TRIM(-99999)); --> VARCHAR(11) (Left Justified)
SELECT TYPE(POSITION('x' IN 'ABCXYZ'));  --> INTEGER
SELECT TYPE(POSITION('1' IN TRIM(2213)));  --> INTEGER
SELECT TYPE(SUBSTRING('this text' FROM 6 FOR 4));  --> VARCHAR(4)
SELECT TYPE(SUBSTRING(12345 FROM 2));  --> VARCHAR(5) (Left Justified)
SELECT TYPE(ADD_MONTHS(DATE, 2)); --> DATE
SELECT TYPE(EXTRACT(DAY FROM DATE)); --> INTEGER


DEFAULT returns the default value for a column. This value is then treated as is any other projected value.
SELECT DEFAULT(c1) AS a,
       SUBSTRING(DEFAULT(c2) FROM 3 FOR 2) AS b, 
       DEFAULT(c3) + c4 AS c,
       DEFAULT(c3) + DEFAULT(c4) AS d
FROM abc
;
The table must have rows of data for this to return a result. Hence, this table has one row.

a           b             c            d        
----------  --  -----------  -----------
Tuba        oe            0                
             

TITLE: returns the name of the title for a column. If it was not titled, it returns the column name. Assume that a column has this definition

Col1 INTEGER (TITLE 'Column 1')

Then this SELECT TITLE(Col1) will return "Column 1" as the columns new heading (which is only a title and not an name).

No comments:

Post a Comment