Teradata Builtin Values and Functions

Built in Value               Value Returned            Data Type                _ACCOUNT                      Current User Account      VARCHAR(30)
CURRENT_DATE                 Current Date              DATE
CURRENT_TIME [(n)]           Current Time              TIME(n) WITH TIMEZONE
CURRENT_TIMESTAMP [(n)]      Current Date/Time         TIMESTAMP(n) WITH TIMEZONE
DATABASE                     Current Default DB        VARCHAR(30)
DATE                         Current Date              DATE
NULL                         The NULL value            As required
PARTITION                    The Index Partition       INTEGER
ROWID                        The Internal Row Id       INTEGER
SESSION                      Current Session Number    INTEGER
TIME                         Current Time              FLOAT
USER                         Current User Id           VARCHAR(30)

      Descriptor Functions                Value Returned            Data Type  _BYTE[S]     (arg)                     Length of arg              INTEGER
CHAR[ACTERS] (string)                  Length of str              INTEGER
MCHAR[ACTERS] (string)                 Length (Mbyte Chars)       INTEGER
CHAR[ACTER]_LENGTH(string)             Length of str (Chars)      INTEGER
OCTET_LENGTH (string [charset])        Length of str (Bytes)      INTEGER
FORMAT      (arg)                     Format of arg              VARCHAR(30)
NAMED       (arg)                     Name assigned to arg       VARCHAR(30)
TITLE       (arg)                     Title on arg               VARCHAR(60)
TYPE        (arg)                     Data Type of arg           VARCHAR(??)

         Math Functions                   Value Returned            Data Type  _ABS         (num)                      Absolute value             Same as arg
ACOS        (num)                      ArcCosine                  FLOAT
ACOSH       (num)                      Hyperbolic ArcCosine       FLOAT
ASIN        (num)                      ArcSine                    FLOAT
ASINH       (num)                      Hyperbolic ArcSine         FLOAT
ATAN        (num)                      ArcTangent                 FLOAT
ATANH       (num)                      Hyperbolic ArcTangent      FLOAT
COS         (num)                      Cosine                     FLOAT
COSH        (num)                      Hyperbolic Cosine          FLOAT
EXP         (num)                      e to the power arg         FLOAT
LOG         (num)                      Base 10 Logarithm          FLOAT
LN          (num)                      Base e  Logarithm          FLOAT
SIN         (num)                      Sine                       FLOAT
SINH        (num)                      Hyperbolic Sine            FLOAT
SQRT        (num)                      Square Root                FLOAT
TAN         (num)                      Tangent                    FLOAT
TANH        (num)                      Hyperbolic Tangent         FLOAT

     Conversion Functions                 Value Returned            Data Type  _

ADD_MONTHS  (date, n)                  Add ‘n’ months to date     DATE
            (timestamp, n)                                         TIMESTAMP

CASE  WHEN exp1 THEN val1              Value substitution         Same as valn
     [WHEN exp2 THEN val2]
           ... [ELSE valn] END

CASE_N      (expr [.. ,expr] [,NO CASE [OR UNKNOWN]] [,UNKNOWN])   INTEGER
CAST        (expr AS typeinfo)         Type/Format conversion     as in typeinfo
CHAR2HEXINT (string)                   HEX display of string      VARCHAR
COALESCE    (expr1, expr2 [.. ,exprn]) First non-Null expr        Same as expr
DATE        ‘YYYY-MM-DD’               YYYY-MM-DD as a date       DATE
EXTRACT     (part FROM date)           Day, Hour, Minute etc      ‘part’ type
HASHROW     (expr, expr)               The Row Hash
HASHBUCKET  (expr)                     The Hash Bucket
HASHAMP     (expr)                     The Primary AMP
HASHBACKAMP (expr)                     The Backup AMP
INDEX       (string, substr)           Start pos. of substr       INTEGER
LOWER       (string)                   String in LowerCase        CHAR
MINDEX      (MBstring, substr)         Start pos. of substr       INTEGER
NULLIF      (expr1, expr2)             Null if e1=e2 else e1      Same as expr1
NULLIFZERO  (num)                      NULL if arg is Zero        Same as arg
(start,end) OVERLAPS (start,end)       Dates/intervals overlap?   BOOLEAN
POSITION    (str1 IN str2)             Start pos of str1 in str2  INTEGER

RANGE_N     (expr BETWEEN start [AND end] [EACH size] [..., ]
            [,NOT IN RANGE [OR UNKNOWN]] [,UNKNOWN] )              INTEGER

RANDOM      (low-bound, high_bound)    A random number            FLOAT
SOUNDEX     ( )
SUBSTR      (string, start, len)       Sub-String of string       CHAR

       [BOTH    ]
TRIM ([[LEADING ] [chr] FROM] string)  Remove blanks (or chr)     CHAR
       [TRAILING]

UPPER       (string)                   String in UpperCase        CHAR
WIDTH_BUCKET ( )
ZEROIFNULL  (num)                      Zero if arg is NULL        Same as arg

Aggregate and OLAP Functions                              Description             AVG         ([DISTINCT] arg)                              Average value
CORREL      (y, x)                                        Correlation
COUNT       ([DISTINCT] arg)                              Number occurrences
COUNT       (*)                                           Number of rows
COVARIANCE  (y, x)                                        Covariance
COVAR_SAMP  (y, x)                                        Sample Covariance
CSUM        (col, sort-expr [… ,sort-expr] )              Cumulative Sum
GCOUNT      (col)                                         ?
GSUM        (col)                                         ?
KURTOSIS    (arg)                                         Kurtosis               
LINREGSLOPE (y, x)                                        Slope: Linear Reg
LINREGINTERCEPT(y, x)                                     Intercept: Linear Reg
MAX[IMUM]   (arg)                                         Maximum value
MIN[IMUM]   (arg)                                         Minimum value
MAVG        (col, #rows, sort-expr [… ,sort-expr] )       Moving Average
MDIFF       (col, #rows, sort-expr [… ,sort-expr] )       Moving Difference
MLINREG     (col, #rows, sort-expr )                      Linear Regression
MSUM        (col, #rows, sort-expr [… ,sort-expr] )       Moving Sum
PERCENT_RANK() OVER ([PARTITION BY spec] ORDER BY spec [ASC | DESC])
QUANTILE    (#partitions, sort-expr [… ,sort-expr] )      Quantile position
RANK        (sort-expr [… ,sort-expr] )                   Rank Position
RANK        () OVER ([PARTITION BY spec] ORDER BY spec [ASC | DESC])
REGR_AVGX   (y, x)                                        Avg of x values in regression
REGR_AVGY   (y, x)                                        Avg of y values in regression
REGR_COUNT  (y, x)                                        # non-null pairs in regression
REGR_R2     (y, x)                                        R squared of Regression
REGR_SXX    (y, x)                                        ? in regression
REGR_SXY    (y, x)                                        ? in regression
REGR_SYY    (y, x)                                        ? in regression
SKEW        ([DISTINCT] arg)                              Skew                   
STDEV       (arg)                                         Standard Deviation     
STDEVP      (arg)                                         Standard Deviation (pop)  
STDDEV_POP  ([DISTINCT] arg)                              Population Std Deviation  
STDDEV_SAMP ([DISTINCT] arg)                              Sample Std Deviation      
SUM         ([DISTINCT] arg)                              Sum of values          

SUM         (arg) OVER ( [PARTITION BY spec]
                         [ORDER BY     spec]
                         ROWS {windowsize} PRECEDING [ASC | DESC] )
                              {UNBOUNDED }

VARIANCE      (arg)  Variance     
VARIANCEP     (arg)  Population Variance
VAR_POP       ([DISTINCT] arg)     Population Variance 
VAR_SAMP      ([DISTINCT] arg)     Sample Variance