17.10 - Related Information - Advanced SQL Engine - Teradata Database
Teradata Vantage™ - SQL Functions, Expressions, and Predicates
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
kby1472250656485
Introduction to SQL Functions, Expressions, and Predicates
Changes and Additions
Functions, Operators, Expressions, and Predicates
SQL Functions
Types of SQL Functions
Examples of Functions
Embedded Services System Functions
Activating Embedded Services System Functions
Invoking Embedded Services System Functions
Implicit Data Type Conversion Rules
Related Information
SQL Operators
SQL Expressions
Types of Expressions
Examples of Expressions
SQL Predicates
Types of Logical Predicates
Logical Operators that Operate on Predicates
Predicate Quantifiers
Examples of Predicates
Aggregate Functions
About Aggregate Functions
Aggregates in the Select List
Aggregates and GROUP BY
Aggregates and Date
Aggregates and Literal Expressions in the Select List
Nesting Aggregates
Results of Aggregation on Zero Rows
Aggregates and Nulls
Aggregate Operations on Floating Point Data
Aggregates and LOBs
Aggregates and Period Data Types
Aggregates and SELECT AND CONSUME Statements
Aggregates and Recursive Queries
Aggregates in WHERE and HAVING Clauses
DISTINCT Option
Aggregates and Row Level Security Tables
Time Series Aggregate Functions Overview
Traditional Aggregate Functions that Support Time Series
Related Information
AVG
AVG Function Syntax
ANSI Compliance
AVG Usage Notes
Computation of INTEGER or DECIMAL Values
Example: Using the AVG Function
Related Information
CORR
CORR Function Syntax
ANSI Compliance
Result Type and Attributes
CORR Usage Notes
Support for UDTs
Combination With Other Functions
Example: Querying Data from the HomeSales Table
Related Information
COUNT
COUNT Function Syntax
Result Type and Attributes
Usage Notes
Examples: Using the COUNT Function
Related Information
COVAR_POP
COVAR_POP Function Syntax
ANSI Compliance
Result Type and Attributes
COVAR_POP Usage Notes
Support for UDTs
Combination With Other Functions
COVAR_SAMP
COVAR_SAMP Function Syntax
ANSI Compliance
Result Type and Attributes
COVAR_SAMP Usage Notes
Support for UDTs
Combination with Other Functions
Example: Using the SELECT statement to Return the Sample Covariance of Weight and Height
GROUPING
GROUPING Function Syntax
ANSI Compliance
Result Type and Attributes
Usage Notes
Example: Viewing Sales Summaries by County and by City
Related Information
KURTOSIS
KURTOSIS Function Syntax
ANSI Compliance
KURTOSIS Usage Notes
Support for UDTs
Conditions That Produce a NULL Return Value
MAXIMUM
MAXIMUM Function Syntax
ANSI Compliance
Result Type and Attributes
Usage Notes
Support for UDTs
Examples: Using the MAXIMUM Function
MINIMUM
MINIMUM Function Syntax
ANSI Compliance
Result Type and Attributes
Usage Notes
Support for UDTs
Examples: Using the MINIMUM Function
PIVOT
PIVOT Function Syntax
Usage Notes
Examples
Example: Alias Names Contained in the IN List
Example: Naming Columns with the <column_value_list> Values
Example: Pivot Operation on View
Example: Table Source Using the WITH Clause
Example: SELECT Query with the WHERE Condition
Example: CREATE TABLE AS Statement Contains Special Characters
Example: The PIVOT Query Response in Different Response Modes
Example: Pivot Query Truncates the Alias Name
Example: Using TD_UNPIVOT or UNPIVOT as a Source to PIVOT
Example: Aggregation on Two Columns from PIVOT Results
Example: Subquery in PIVOT IN-List
Related Information
REGR_AVGX
REGR_AVGX Function Syntax
ANSI Compliance
Result Type and Attributes
REGR_AVGX Usage Notes
Support for UDTs
Setting Up Axes for Plotting
Combination With Other Functions
Example: Returning the Mean Height for regrtbl
REGR_AVGY
REGR_AVGY Function Syntax
ANSI Compliance
Result Type and Attributes
REGR_AVGY Usage Notes
Support for UDTs
Setting Up Axes for Plotting
Combination With Other Functions
Example: Returning the Mean Weight from regrtbl
Related Information
REGR_COUNT
REGR_COUNT Function Syntax
ANSI Compliance
Result Type and Attributes
REGR_COUNT Usage Notes
Support for UDTs
Setting Up Axes for Plotting
Combination With Other Functions
Example: Returning the Number of Rows in regrtbl
Related Information
REGR_INTERCEPT
REGR_INTERCEPT Function Syntax
ANSI Compliance
Result Type and Attributes
REGR_INTERCEPT Usage Notes
Support for UDTs
Setting Up Axes for Plotting
Combination With Other Functions
Example: Returning the Intercept of the Regression Line for NbrSold and SalesPrice
Related Information
REGR_R2
REGR_R2 Function Syntax
ANSI Compliance
Result Type and Attributes
REGR_R2 Usage Notes
Support for UDTs
Setting Up Axes for Plotting
Combination With Other Functions
Example: Returning the Coefficient of Determination for Height and Weight
Related Information
REGR_SLOPE
REGR_SLOPE Function Syntax
ANSI Compliance
Result Type and Attributes
REGR_SLOPE Usage Notes
Support for UDTs
Setting Up Axes for Plotting
Combination With Other Functions
Example: Returning the Slope of the Regression Line for NbrSold and SalesPrice
Related Information
REGR_SXX
REGR_SXX Function Syntax
ANSI Compliance
Result Type and Attributes
REGR_SXX Usage Notes
Support for UDTs
Setting Up Axes for Plotting
Combination With Other Functions
Example: Returning the Sum of Squares for Height
Related Information
REGR_SXY
REGR_SXY Function Syntax
ANSI Compliance
Result Type and Attributes
REGR_SXY Usage Notes
Support for UDTs
Setting Up Axes for Plotting
Combination With Other Functions
Example: Returning the Sum of Products of Height and Weight
Related Information
REGR_SYY
REGR_SYY Function Syntax
ANSI Compliance
Result Type and Attributes
REGR_SYY Usage Notes
Support for UDTs
Setting Up Axes for Plotting
Combination With Other Functions
Example: Returning the Sum of Squares for Weight
SKEW
SKEW Function Syntax
Related Information
STDDEV_POP
STDDEV_POP Function Syntax
ANSI Compliance
STDDEV_POP Usage Notes
Combination With Other Functions
How GROUP BY Affects Report Breaks
Measuring the Standard Deviation of a Population
Related Information
STDDEV_SAMP
STDDEV_SAMP Function Syntax
ANSI Compliance
STDDEV_SAMP Usage Notes
Combination With Other Functions
How GROUP BY Affects Report Breaks
Measuring the Standard Deviation of a Population
Related Information
SUM
SUM Function Syntax
Return Values
ANSI Compliance
Usage Notes
Possible Result Overflow with SELECT Sum
Examples
Example: Accounts Receivable
Example: Face Value of Inventory
Related Information
UNPIVOT
UNPIVOT Function Syntax
Usage Notes
Examples
Example: Unpivoted Sales and Cogs Columns
Example: Using UNPIVOT for a Unique Year Value
Example: Normalizing the UNPIVOT Operation
Example: Using UNPIVOT with the INCLUDE NULLS Clause
Example: Using UNPIVOT with the EXCLUDE NULLS Clause
Example: Using an IN List with Multiple Column Lists and Unspecified Aliases
Example: Using an IN List that Contains Multiple Columns with a Compatible Data Type
Example: Using an IN List that Contains Multiple Columns with an Incompatible Data Type
Related Information
VAR_POP
VAR_POP Function Syntax
ANSI Compliance
VAR_POP Usage Notes
Combination With Other Functions
GROUP BY Affects Report Breaks
Measuring the Standard Deviation of a Population
Related Information
VAR_SAMP
VAR_SAMP Function Syntax
ANSI Compliance
VAR_SAMP Usage Notes
Combination With Other Functions
GROUP BY Affects Report Breaks
Measuring the Variance of a Population
Related Information
Arithmetic, Trigonometric, Hyperbolic Operators/Functions
ANSI Compliance
Arithmetic Operators and LOBs
Arithmetic Operators and UDTs
Related Information
Binary Arithmetic Result Data Types
Result Data Type
DECIMAL Result Data Type
Numeric Results and Rounding
Error Conditions
Integer Division and Truncation
Structure of Arithmetic Expressions
Order of Evaluation
Format
Example: Determining Employee Salary Increases
Arithmetic Functions
ABS
ABS Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Examples: Using ABS Arithmetic Function Expressions
CASE_N
CASE_N Function Syntax
ANSI Compliance
Result Type and Attributes
CASE_N Usage Notes
Using CASE_N to Define Partitioned Primary Indexes
Using CASE_N with CURRENT_DATE or CURRENT_TIMESTAMP in a PPI
Using CASE_N with Character Comparison
Restrictions
Examples
Example: Defining the Partition to Which a Row is Assigned
Example: Using CASE_N in a List of Partitioning Expressions that Define a Multilevel PPI
Example: Showing the Count of Rows in Each Partition
Example: Creating a Table Partitioned with Orders Data
Example: Verifying the Ending Bound of a Period Expression
Example: Verifying the Ending Bound of a Transaction Time Column
Example: Viewing Results for FALSE Conditions
Example: Viewing Results for UNKNOWN Conditions
Example: Defining Partitions Based on the Value of a
Related Information
CEILING
CEILING Function Syntax
ANSI Compliance
Result Type and Attributes
Examples
DEGREES/RADIANS
DEGREES/RADIANS Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Format
Usage Notes
Examples: Representative DEGREES/RADIANS Function Expressions
EXP
EXP Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Usage Notes
Examples: Representative EXP Arithmetic Function Expressions
Related Information
FLOOR
FLOOR Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Examples: Using the FLOOR Function
Related Information
HYPERBOLIC
HYPERBOLIC Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Related Information
LN
LN Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Examples: Representative LN Arithmetic Function Expressions
Related Information
LOG
LOG Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Example: Representative LOG Arithmetic Function Expressions
Related Information
MOD
MOD Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Example: Using MOD Arithmetic Function Expression
Related Information
NULLIFZERO
NULLIFZERO Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Examples
Related Information
POWER
POWER Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Examples: Querying Returns for the Power of exponent_value
Related Information
RANDOM
RANDOM Function Syntax
ANSI Compliance
Result Type and Attributes
RANDOM Usage Notes
Restrictions
Multiple RANDOM Calls Within a SELECT List
Using RANDOM as a Condition on an Index
Example: Returning Random Integer Numbers as Results
Related Information
RANGE_N
RANGE_N Function Syntax
ANSI Compliance
Result Type and Attributes
RANGE_N Usage Notes
Restrictions
Using RANGE_N to Define Partitioned Primary Indexes
Using RANGE_N with CURRENT_DATE or CURRENT_TIMESTAMP in a PPI
Using RANGE_N with Character Data
Using a UDT as the Test Expression
Examples
Related Information
ROUND
ROUND Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Usage Notes
Examples
Related Information
SIGN
SIGN Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Usage Notes
Examples
Related Information
SQRT
SQRT Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Examples: Representative SQRT Arithmetic Function Expressions
Related Information
TRIGONOMETRIC
TRIGONOMETRIC Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Examples: Representative Function Expressions
Related Information
TRUNC
TRUNC Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Usage Notes
Examples: Query Returns for SELECT TRUNC
Related Information
WIDTH BUCKET
WIDTH BUCKET Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
WIDTH BUCKET Usage Notes
Rules
Example: Using WIDTH BUCKET to Create a Histogram for Employee Salaries within a Range
Related Information
ZEROIFNULL
ZEROIFNULL Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Example: Testing the Salary Column for Null
Related Information
Attribute Functions
ANSI Equivalence of Teradata Attribute Functions
BIT_LENGTH
BIT_LENGTH Function Syntax
ANSI Compliance
Example
Related Information
BYTE/BYTES
BYTE/BYTES Function Syntax
ANSI Compliance
BYTE/BYTES Usage Notes
Length Includes Trailing Zeros
Example: Using BYTE to Obtain the Number of Bytes in a Badge Picture
Related Information
CHARACTER_LENGTH
CHARACTER_LENGTH Function Syntax
ANSI Compliance
Result Type
Usage Notes
Suppressing Trailing Pad Characters
Examples
Related Information
DEFAULT
DEFAULT Function Syntax
ANSI Compliance
Result Type and Attributes
DEFAULT Usage Notes
Omitting the Column Name
Using a Qualified Column Name
Error Conditions
Examples
Example: Inserting the Default Value under Certain Conditions
Example: Using DEFAULT in a Predicate
Example: Specifying a View Column Name
Related Information
FORMAT
FORMAT Function Syntax
ANSI Compliance
Result Type and Attributes
Example: Requesting the Format of the Salary Column
Related Information
OCTET_LENGTH
OCTET_LENGTH Function Syntax
ANSI Compliance
Usage Notes
Examples: Output from OCTET_LENGTH
Related Information
TITLE
TITLE Function Syntax
ANSI Compliance
Result Type and Attributes
Usage Notes
Example: Requesting the title of the Salary Column
Related Information
TYPE
TYPE Function Syntax
ANSI Compliance
Result Type and Attributes
TYPE Usage Notes
Character Type Arguments
Examples
Related Information
Bit/Byte Manipulation Functions
Bit and Byte Numbering Model
BYTEINT
SMALLINT
INTEGER
BIGINT
BYTE and VARBYTE
HEXADECIMAL BYTE LITERALS
Performing Bit-Byte Operations against Arguments with Non-Equal Lengths
Example: Querying the BITAND Operation On An INTEGER
BITAND
BITAND Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Examples: Querying with the BITAND Function
Related Information
BITNOT
BITNOT Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Example: Querying with the BITNOT Function
BITOR
BITOR Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Example: Querying with the BITOR Function
BITXOR
BITXOR Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Example: Querying with the BITXOR Function
Related Information
COUNTSET
COUNTSET Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Example: Querying with the COUNTSET Function
Related Information
GETBIT
GETBIT Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Example: Querying with the GETBIT Function
Related Information
ROTATELEFT
ROTATELEFT Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Examples
Example: Querying Input Argument 16 with the ROTATELEFT Function
Example: Querying Input Argument 64 with the ROTATELEDT Function
Related Information
ROTATERIGHT
ROTATERIGHT Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Examples
Example: Querying Input Argument 32 with the ROTATERIGHT Function
Example: Querying Input Argument 4 with the ROTATERIGHT Function
Related Information
SETBIT
SETBIT Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Examples
Example: Querying with the SETBIT Function
Example: Querying Input Argument 23 with the ROTATERIGHT Function
Related Information
SHIFTLEFT
SHIFTLEFT Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Example: Querying with the SHIFTLEFT Function
Related Information
SHIFTRIGHT
SHIFTRIGHT Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Example: Querying with the SHIFTRIGHT Function
Related Information
SUBBITSTR
SUBBITSTR Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Example: Querying with the SUBBITSTR Function
Related Information
TO_BYTE
TO_BYTE Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Example: Querying with the TO_BYTE Function
Related Information
Built-In Functions
ACCOUNT
ACCOUNT Function Syntax
ANSI Compliance
Result Type and Attributes
Usage Notes
Example: Requesting Account Strings for a User
CURRENT_DATE/CURDATE
CURRENT_DATE/CURDATE Function Syntax
ANSI Compliance
Result Type and Attributes
Usage Notes
Examples
Example: Returning the Current Date for INTERVAL -'08:00' HOUR TO MINUTE
Example: Returning the Current Date for INTERVAL -'09:00' HOUR TO MINUTE
Example: Returning the Current Date for INTERVAL -'05:45' HOUR TO MINUTE
Example: Returning the Current Date for the Time Zone String, 'America Pacific'
Example: Changing the Default Output Format
Related Information
CURRENT_ROLE
CURRENT_ROLE Function Syntax
ANSI Compliance
Result Type and Attributes
CURRENT_ROLE Usage Notes
Result Value
Example: Selecting CURRENT_ROLE
CURRENT_TIME/CURTIME
CURRENT_TIME/CURTIME Function Syntax
ANSI Compliance
Result Type and Attributes
Usage Notes
Examples
Example: Requesting the Current Time
Example: Requesting the Current Time with a Time Zone String
Example: Changing the Default Output Format
Related Information
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP Function Syntax
ANSI Compliance
Result Type and Attributes
Usage Notes
Examples
Example: Requesting the Current Timestamp
Example: CURRENT_TIMESTAMP and the TimeDateWZControl Flag
Example: Requesting the Current Timestamp with a Time Zone String
Example: Changing the Default Output Format
Related Information
CURRENT_USER
CURRENT_USER Function Syntax
ANSI Compliance
Result Type and Attributes
CURRENT_USER Usage Notes
Result Value
Examples
Example: Identifying the Current User
Example: Selecting the Job Title for the Current User
DATABASE
DATABASE Function Syntax
ANSI Compliance
Result Type and Attributes
Example: Requesting the Name of the Default Database
DATE
DATE Function Syntax
ANSI Compliance
Result Type and Attributes
Usage Notes
DATE versus CURRENT_DATE
Examples
Related Information
NOW
NOW Function Syntax
ANSI Compliance
Result Type and Attributes
Example
Related Information
PROFILE
PROFILE Function Syntax
ANSI Compliance
Result Type and Attributes
Example
ROLE
ROLE Function Syntax
ANSI Compliance
Result Type and Attributes
ROLE Usage Notes
Result Value
Session logon is not directory-based
Session logon is directory-based
Example: Identifying the Session Current Role
SESSION
SESSION Function Syntax
ANSI Compliance
Result Type and Attributes
Example: Identifying the Session Number for the Current User
TEMPORAL_DATE
TEMPORAL_DATE Function Syntax
Result Type and Attributes
Usage Notes
TEMPORAL_TIMESTAMP
TEMPORAL_TIMESTAMP Function Syntax
Result Type and Attributes
Usage Notes
Related Information
TIME
TIME Function Syntax
ANSI Compliance
Result Type and Attributes
Usage Notes
Examples
Related Information
USER
USER Function Syntax
ANSI Compliance
Result Type and Attributes
Examples
Example: Identifying the User Name
Example: Selecting the User Job Title
Related Information
ZONE
ZONE Function Syntax
ANSI Compliance
Result Type and Attributes
Example
Comparison Operators and Functions
Comparison Operators
Comparison Operator Syntax
ANSI Compliance
Results
Comparison Operators Usage Notes
Supported Comparison Operators
Comparison Operators Using Subqueries
Example: Using the ALL Quantifier to Compare Two Expressions
Related Information
Comparisons That Produce TRUE Results
Conditions
Null Expressions
Floating Point Expressions
Related Information
Data Type Evaluation
Implicit Type Conversion of Comparison Operands
Data Types on Which Implicit Conversion is Performed
Implicit Conversion of DateTime Types
Data Types on Which Implicit Conversion is Not Performed
Comparison of ANSI DateTime and Interval in USING Clause
Proper Forms of DATE Types in Comparisons
Examples
Character String Comparisons
Comparison of Character Strings of Unequal Length
Character Strings and Server Character Sets
Effect of Collation on Character String Comparisons
Case Sensitivity
Using UPPER for Case Blind Comparisons
Example: Querying for Case-Specific Names
Related Information
Comparison of KANJI1 Characters
Equality Comparison
Nonequality Comparison
Related Information
Comparison Operators and the DEFAULT Function in Predicates
DECODE
DECODE Function Syntax
Argument Types and Rules
ANSI Compliance
Usage Notes
Result Type
Examples
Example: Decoding IDs
Example: Decoding IDs Using NULL
Example: Decoding IDs When ID is Not Equal to 1, 2 or NULL
Related Information
GREATEST
GREATEST Function Syntax
Argument Types and Rules
ANSI Compliance
Usage Notes
Result Type
Examples
Related Information
LEAST
LEAST Function Syntax
Argument Types and Rules
ANSI Compliance
Usage Notes
Result Type
Examples
Related Information
CASE Expressions
Valued CASE Expression
Valued CASE Expression Syntax
ANSI Compliance
Usage Notes
Restrictions on the Data Types in a CASE Expression
Examples
Example: Calculating the Fraction of Cost
Example: Using a CASE Expression
Example: Combining a CASE Expression with a Concatenation Operator
Example: Using UDT Data Types in Value Expressions
Example 1: Using UDT Data Types in Scalar Expressions
Related Information
Searched CASE Expression
Searched CASE Expression Syntax
ANSI Compliance
Usage Notes
Rules for WHEN Search Conditions
Restrictions on the Data Types in a CASE Expression
Examples
Example: Evaluating a Search Condition
Example: Using a CASE Expression
Example: Using an ELSE Clause
Example: Using a CASE expression to Enhance Performance
Example: Producing a Report to Show Employee Salary
Related Information
Error Conditions
Rules for the CASE Expression Result Type
THEN/ELSE Expressions Having the Same Non-Character Data Type
THEN/ELSE Character Type Expressions
Examples
Examples of Character Data in a CASE Expression
Examples of Character Data in a CASE Expression: Example 1
Examples of Character Data in a CASE Expression: Example 2
Examples of Character Data in a CASE Expression: Example 3
Examples of Character Data in a CASE Expression: Example 4
THEN/ELSE Expressions Having Mixed Data Types
Examples of Numeric Data in a CASE Expression
Example: CASE Expression Fails
Example: Shortening the Scale of the Multiplier
Example: Returning a DECIMAL(38,2) Result
Examples of Character and Numeric Data in a CASE Expression
Examples of Character and Numeric Data in a CASE Expression: Example 1
Examples of Character and Numeric Data in a CASE Expression: Example 2
Related Information
Format for a CASE Expression
Default Format
Using Explicit Type Conversion to Change Format
CASE and Nulls
CASE Shorthands
COALESCE Expression
COALESCE Expression Syntax
ANSI Compliance
Usage Notes
Restrictions on the Data Types in a COALESCE Expression
Examples
Example: Querying for a Phone Number
Example: Using COALESCE with an Arithmetic Operator
Example: Using COALESCE with an Comparison Operator
Related Information
NULLIF Expression
NULLIF Expression Syntax
ANSI Compliance
Usage Notes
Restrictions on the Data Types in a NULLIF Expression
Examples
Related Information
Hash-Related Functions
HASHAMP
HASHAMP Function Syntax
ANSI Compliance
Usage Notes
Examples
Example Assumptions
Example: Querying the Distribution of Hash Buckets
Example: Querying the Number of Rows on Each Primary AMP
Example: HASHAMP with a Contiguous Map
Example: HASHAMP with an Expression and a Contiguous Map
Example: Which AMPs Contain the Rows of a Table
Related Information
HASHBAKAMP
HASHBAKAMP Function Syntax
ANSI Compliance
Usage Notes
Examples
Example Assumptions
Example: Distributing the Hash Buckets Among the Fallback AMPs
Example: Which Fallback AMPs Contain the Rows of a Table
Related Information
HASHBUCKET
HASHBUCKET Function Syntax
ANSI Compliance
HASHBUCKET Usage Notes
Using HASHBUCKET to Convert a BYTE Type to an INTEGER Type
Examples
Example Assumptions
Example
Example
Example
Example
Example
HASHROW
HASHROW Function Syntax
ANSI Compliance
Usage Notes
Examples
Example
Example
Example
Related Information
Logical Predicates
About Logical Predicates
Where Logical Predicates Are Used
Conditional Expressions as a Collection of Logical Primitives
Restrictions on the Data Types Involved in Predicates
Restrictions on the DEFAULT Function in a Predicate
Logical Operators and Search Conditions
Logical Operators
Where To Use Search Conditions
Rules for Order of Evaluation
Evaluation Results
AND Truth Table
OR Truth Table
NOT Truth Table
Subquery Restrictions
Examples of Logical Operators in Search Conditions
ANY/ALL/SOME
ANY/ALL/SOME Predicate Syntax
ANSI Compliance
ANY/ALL/SOME Usage Notes
ANY/ALL/SOME Quantifiers and Literal Syntax
ANY/ALL/SOME Quantifiers and Subquery Syntax
Equivalences Using ANY/ALL/SOME and Comparison Operators
Equivalences Using ANY/ALL/SOME and IN/NOT IN
Examples
Example: ANY Quantifier
Example: ALL Quantifier
Example: ANY/ALL/SOME
BETWEEN/NOT BETWEEN
BETWEEN/NOT BETWEEN Predicate Syntax
ANSI Compliance
Usage Notes
Example
EXISTS/NOT EXISTS
EXISTS/NOT EXISTS Predicate Syntax
ANSI Compliance
EXISTS/NOT EXISTS Usage Notes
Relationship Between EXISTS/NOT EXISTS and IN/NOT IN
Example
EXISTS Predicate Versus NOT IN and Nulls
For ALL
NOT EXISTS Clauses and Stored Procedures
NOT EXISTS and Recursive Queries
Examples
Example: EXISTS with Correlated Subqueries
Example: NOT EXISTS with Correlated Subqueries
Related Information
IN/NOT IN
IN/NOT IN Predicate Syntax
ANSI Compliance
IN/NOT IN Usage Notes
Result
Relationship Between IN/NOT IN and EXISTS/NOT EXISTS
Equivalences Using IN/NOT IN, NOT, and ANY/ALL/SOME
Behavior of Nulls for IN
Behavior of Nulls for NOT IN
NOT IN Clauses and Stored Procedures
NOT IN and Recursive Queries
Queries With Large NOT IN Clauses Can Fail
Examples
Example: Searching for Atlanta Employees
Example: Searching when DeptNo Has Two Columns
Example: Using IN/NOT IN with a List of Literals
IS NULL/IS NOT NULL
IS NULL/IS NOT NULL Predicate Syntax
ANSI Compliance
Examples
Example
Example
Example: Searching for NULL and NOT-NULL in the Same Statement
Example: Searching a Table That Might Contain Nulls
LIKE/NOT LIKE
LIKE/NOT LIKE Predicate Syntax
ANSI Compliance
LIKE/NOT LIKE Usage Notes
Optimized Performance Using a NUSI
Null Expressions
Case Specification
Wildcard Characters
ESCAPE Feature of LIKE
Pad Characters
ANY/ALL/SOME Quantifiers
Behavior of the ESCAPE Character
LIKE/NOT LIKE Examples
Example: ESCAPE
Example: ANY
Example: Matching Patterns from Another Table
Example: LIKE Predicate
Example: Last Name Spelling
Example: % and _ Characters
Example: Pad Characters and Letter
KanjiEBCDIC Examples
KanjiEUC Examples
KanjiShift-JIS Examples
Miscellaneous Examples
OVERLAPS
Null-Handling Functions
NVL
NVL Function Syntax
Argument Types and Rules
Result Type
Example
Related Information
NVL2
NVL2 Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Examples
Example
Example
Related Information
Ordered Analytical/Window Aggregate Functions
Ordered Analytical Functions
Benefits
Ordered Analytical Calculations at the SQL Level
Teradata Warehouse Miner
Example
Characteristics of Ordered Analytical Functions
The Function Value
Use of QUALIFY Clause
DISTINCT Clause Restriction
Permitted Query Objects
Where Ordered Analytical Functions are Not Permitted
Use of Standard SQL Features
Unsupported Data Types
Ordered Analytical Functions and Period Data Types
Ordered Analytical Functions and Recursive Queries
Ordered Analytical Functions and Hash or Join Indexes
Ordered Analytical Functions and Row Level Security Tables
Computation Sort Order and Result Order
Data in Partitioning Column of Window Specification and Resource Impact
Using Ordered Analytical Functions
Example: Using QUALIFY With RANK
Example: Using QUALIFY With RANK
Related Information
The Window Feature
PARTITION BY Phrase
ORDER BY Phrase
RESET WHEN Phrase
RESET WHEN Condition Rules
Rules For Using a DEFAULT Function As Part of a RESET WHEN Condition
Examples
Example
Example
Example
Example
ROWS Phrase
Multiple Window Specifications
Related Information
Window Aggregate Functions
The Window Specification
Window Specification Syntax
ANSI Compliance
Type of Computation
Arguments to Window Aggregate Functions
Result Type and Format
Result Title
Problems with Missing Data
Nesting Aggregates in Window Functions
Alternative: Using Derived Tables
Teradata-Specific Alternatives to Ordered Analytical Functions
Teradata-Specific Functions and ANSI SQL:2011 Window Functions
Comparing Window Aggregate Functions and Teradata-Specific Functions
Example: Group Count
Example: Remaining Count
Example: Cumulative Maximum
Example: Cumulative Minimum
Example: Cumulative Sum
Example: Group Sum
Example: Group Sum
Example: Moving Sum
Example: Remaining Sum
GROUP BY Clause
GROUP BY and Window Functions
GROUP BY and Teradata-Specific Functions
Combining Window Functions, Teradata-Specific Functions, and GROUP BY
Possible Result Overflow with SELECT Sum
Related Information
CSUM
CSUM Function Syntax
ANSI Compliance
Result Type and Attributes
CSUM Usage Notes
Using SUM Instead of CSUM
Possible Result Overflow with SELECT Sum
Examples
Example
Example
CUME_DIST
CUME_DIST Function Syntax
ANSI Compliance
Results
Example
DENSE_RANK (ANSI)
DENSE_RANK Function Syntax (ANSI)
ANSI Compliance
Result Type
Example
FIRST_VALUE/LAST_VALUE
FIRST_VALUE/LAST_VALUE Function Syntax
ANSI Compliance
Usage Notes
Example
LAG/LEAD
LAG/LEAD Function Syntax
ANSI Compliance
Result Type
Usage Notes
Examples
Example: LAG with IGNORE NULLS
Example: LAG with RESPECT NULLS
Example: LAG with RESPECT NULLS without Explicitly Specifying RESPECT NULLS
Example: LEAD with RESPECT NULLS
Example: LEAD with IGNORE NULLS
Example: LEAD with RESPECT NULLS without Explicitly Specifying RESPECT NULLS
MAVG
MAVG Function Syntax
ANSI Compliance
Result Type and Attributes
MAVG Usage Notes
Using AVG Instead of MAVG
Problems With Missing Data
Computing the Moving Average When Number of Rows < width
Examples
Example
Example
MDIFF
MDIFF Function Syntax
ANSI Compliance
Result Type and Attributes
MDIFF Usage Notes
Using SUM Instead of MDIFF
Problems With Missing Data
Computing the Moving Difference When No Preceding Row Exists
Examples
Example
Example
Related Information
MEDIAN
MEDIAN Function Syntax
ANSI Compliance
Example
Related Information
MLINREG
MLINREG Function Syntax
ANSI Compliance
Result Type and Attributes
MLINREG Usage Notes
Using ANSI-Compliant Window Functions Instead of MLINREG
Computing MLINREG When Preceding Rows < width - 1
Example
Related Information
MSUM
MSUM Function Syntax
ANSI Compliance
Result Type and Attributes
MSUM Usage Notes
Using SUM Instead of MSUM
Problems With Missing Data
Computing MSUM When Number of Rows < width
Possible Result Overflow with SELECT Sum
PERCENT_RANK
PERCENT_RANK Function Syntax
ANSI Compliance
Result Type and Attributes
Examples
Example: Relative Rank
Example: Rank and Relative Rank
Example: PERCENT_RANK and CUM_DIST
PERCENTILE_CONT/PERCENTILE_DISC
PERCENTILE_CONT/PERCENTILE_DISC Function Syntax
ANSI Compliance
Usage Notes
Example
QUANTILE
QUANTILE Function Syntax
ANSI Compliance
Result Type and Attributes
QUANTILE Usage Notes
Using ANSI Window Functions Instead of QUANTILE
Examples
Example
Example
Example
Related Information
RANK (ANSI)
RANK Function Syntax (ANSI)
ANSI Compliance
Result Type and Attributes
Examples
Example: Ranking Salespeople Based on Sales
Example: Finding Differences Between RANK(ANSI) and DENSE_ RANK(ANSI)
Related Information
RANK (Teradata)
RANK Function Syntax (Teradata)
ANSI Compliance
Result Type and Attributes
RANK Usage Notes (Teradata)
Using ANSI RANK Instead of Teradata RANK
Computing Top and Bottom Values
Examples
Example
Example
Example
Related Information
ROW_NUMBER
ROW_NUMBER Function Syntax
ANSI Compliance
ROW_NUMBER Usage Notes
Window Aggregate Equivalent
Example
Related Information
Regular Expression Functions
REGEXP_SUBSTR
REGEXP_SUBSTR Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Examples
Example
Example
Related Information
REGEXP_REPLACE
REGEXP_REPLACE Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
REGEXP_REPLACE Usage Notes
Limitation: NULL inside Input Strings
Examples
Example
Example
Example
Related Information
REGEXP_INSTR
REGEXP_INSTR Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Examples
Example
Example
Related Information
REGEXP_SIMILAR
REGEXP_SIMILAR Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Example
Related Information
REGEXP_SPLIT_TO_TABLE
REGEXP_SPLIT_TO_TABLE Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Example
String Operators and Functions
String Operators and Functions
About String Functions
Data Types on Which String Functions can Operate
ANSI Equivalence of Teradata SQL String Functions
Additional Functions That Operate on Strings
Effects of Server Character Sets on Character String Functions
Uppercase Character Conversion for LATIN
Logical Characters vs. Physical Characters
Untranslatable KANJI1 Characters
Implicit Server Character Set Translation
Concatenation Operator
Concatenation Operator Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Concatenation Operator Usage Notes
Concatenating Character Strings Having Different Server Character Sets
Examples
Example: Using Concatenation to Create More Readable Results
Example: Concatenating First Name With Last Name
Example: Concatenating Last Name With First Name
Example: Concatenating Byte Strings
Examples for Japanese Character Sets
Example: KanjiEBCDIC
Example: KanjiEUC
Example: KanjiShift-JIS
Related Information
ASCII
ASCII Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Example
Related Information
CHAR2HEXINT
CHAR2HEXINT Function Syntax
Result Type and Attributes
CHAR2HEXINT Usage Notes
CHAR2HEXINT and Literal Strings
UDT Arguments
Examples
Example
Example
Related Information
CHR
CHR Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Example
Related Information
CONCAT
CONCAT Function Syntax
Argument Type and Rules
ANSI Compliance
Result Type and Attributes
Example
Related Topic
CSV
CSV Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Examples
Example
Example
Related Information
CSVLD
CSVLD Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Example
Related Information
EDITDISTANCE
EDITDISTANCE Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Usage Notes
Examples
Example
Example
Example
Example
Related Information
INDEX
INDEX Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
INDEX Usage Notes
Expected Values
Rules for Character Type Arguments
Rules for KANJI1 Server Character Set
Relationship Between INDEX and POSITION
Examples
Example: Using Simple INDEX Expressions
Example
Example: Using INDEX with KANJI1 and KanjiEBCDIC
Example: Using INDEX with KANJI1 and KanjiEUC
Example: Using INDEX with KANJI1 and KanjiShift-JIS
Example: Applying INDEX to the SPACE Character
Example: Using "Batch" in the Project Description
Example: Using Concatenation, SUBSTRING, and INDEX
Related Information
INITCAP
INITCAP Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Example
Related Information
INSTR
INSTR Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Usage Notes
Examples
Example
Example
Related Information
LEFT
LEFT Function Syntax
Usage Notes
Result Type
Example
LENGTH
LENGTH Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Example
Related Information
LOCATE
LOCATE Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
Examples
LOWER
LOWER Function Syntax
Argument Types
Result Type and Attributes
Usage Notes
UDT Arguments
Example
Related Information
LPAD
LPAD Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Examples
Example
Example
Example
Related Information
LTRIM
LTRIM Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Examples
Example
Example
Related Information
NGRAM
NGRAM Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Usage Notes
Examples
Example
Example
Example
Example
Example
Example
Example
Example
Example
Related Information
NVP
NVP Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Examples
Example: Querying for Entree
Example: Querying for Second Occurrence of 'store'
Example: Querying for Entree (Default Value)
Example: Querying for Entree with 1 Occurrence
Related Information
OREPLACE
OREPLACE Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Usage Notes
Examples
Example
Example
Example
Example
Example
Related Information
OTRANSLATE
OTRANSLATE Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Usage Notes
Examples
Example: Returning the Current Database Version
Example: Removing Extra Characters from the Query Results
Example: Replacing and Returning Query Characters
Example: Removing Characters without Replacing Them
Related Information
POSITION
POSITION Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
POSITION Usage Notes
Expected Values
How POSITION and INDEX Differ
Related Information
REVERSE
REVERSE Function Syntax
Usage Notes
Result Type
Example
RIGHT
RIGHT Function Syntax
Usage Notes
Result Type
Example
RPAD
RPAD Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Examples
Example
Example
Example
Related Information
RTRIM
RTRIM Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Examples
Example
Example
Related Information
SOUNDEX
SOUNDEX Function Syntax
Argument Types
ANSI Compliance
SOUNDEX Usage Notes
Soundex Coding Guide
Examples
Example
Example
Example
Examples of Non Valid Usage
Related Information
STRING_CS
STRING_CS Function Syntax
Argument Types
ANSI Compliance
Result Value
Usage Notes
Examples
Example: Using STRING_CS to Determine the Client Character Set
Example: Using STRING_CS to Translate a KANJI1 String to UNICODE
Related Information
STRTOK
STRTOK Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Usage Notes
Example: Using STRTOK
STRTOK_SPLIT_TO_TABLE
STRTOK_SPLIT_TO_TABLE Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type
Example
SUBSTRING
SUBSTRING Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
SUBSTRING Usage Notes
Result Value
Usage Rules for SUBSTRING and SUBSTR
Difference Between SUBSTRING and SUBSTR
Examples
Example: Searching for Car Serial IDs
Example: Accessing Serial ID Characters
Example: Limiting Returned Characters
Example: Using a SELECT Statement to Request Substrings
Example: Using the CREATE TABLE cstr Table
Example: Differences Between SUBSTR and SUBSTRING
Example: Using the KanjiEUC Client Character Set with the ctable1 Table
Example: Using Examples for the KanjiShift-JIS Client Character Set
Example: Applying the SUBSTRING Function to a CLOB Column
Related Information
TRANSLATE
TRANSLATE Function Syntax
Argument Types
Result Type and Attributes
TRANSLATE Usage Notes
Supported Translations for CLOB Strings
Supported Translations Between Character Sets
Source Characters That Generate Errors
Error Characters Assigned by the WITH ERROR Option
Suffixes
Translations Between Fullwidth and Halfwidth Character Data
Space Folding
UNICODE Normalization Form Translations
Pad Character Translation
Migration
Implicit Character Data Type Conversion
Related Information
TRANSLATE_CHK
TRANSLATE_CHK Function Syntax
Argument Types
ANSI Compliance
Result Type and Attributes
TRANSLATE_CHK Usage Notes
Checking UNICODE Normalization Form Translations
Examples
Example
Example
Example
Example
Related Information
TRIM
TRIM Function Syntax
Argument Types and Rules
ANSI Compliance
Result Type and Attributes
TRIM Usage Notes
Concatenation With TRIM
Examples
Example
Example
Example
Example: Using TRIM Functions
Related Information
UPPER/UCASE
UPPER/UCASE Function Syntax
Argument Types
ANSI Compliance
Result Type and Attributes
Usage Notes
Examples
Example: Using a Table Definition with CASESPECIFIC Attributes
Example: Using UPPER to Store Values
Example: Converting Single Byte Characters to Uppercase
Related Information
VARGRAPHIC
VARGRAPHIC Function Syntax
Argument Types
ANSI Compliance
Result Type and Attributes
VARGRAPHIC Usage Notes
Rules
VARGRAPHIC Function Conversion Tables
Examples
Example
Example
Related Information
User-Defined Functions
SQL UDF
SQL UDF Function Syntax
Required Privileges
ANSI Compliance
Usage Notes
Examples
Example: Defining the Function and Query
Example: Referencing an External UDF
Example: Invoking the SQL UDF
Example: Invoking Compatible Argument Data Types
Example: Invoking Argument Data Types of Different Sizes
Related Information
Elastic TCore Functions
GetSystemTCore
GetSystemTCore Syntax
GetSystemTCore Example
SetSystemTCore
SetSystemTCore Example
SetSystemTCore Syntax
Notation Conventions
How to Read Syntax
Character Shorthand Notation Used in This Document
Additional Information
Teradata Links
For more information on potential problems associated with floating point values in computations, see
Teradata Vantage™ - Data Types and Literals , B035-1143 .
For an explanation of the formatting characters in the format, see “Data Type Formats and Format Phrases” in
Teradata Vantage™ - Data Types and Literals , B035-1143 .
Teradata Vantage™ - Advanced SQL Engine Security Administration , B035-1100
For more information on CREATE CAST, see
Teradata Vantage™ - SQL Data Definition Language Syntax and Examples , B035-1144 .
To disable the AVG extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see
Teradata Vantage™ - Database Utilities
, B035-1102
.
For more information on implicit type conversion of UDTs, see
Teradata Vantage™ - Data Types and Literals , B035-1143 .
For more information on nulls, see
Teradata Vantage™ - SQL Fundamentals , B035-1141 and Aggregates and Nulls .
Aggregate user-defined functions (UDFs), see "Aggregate UDF" in
Teradata Vantage™ - SQL Operators and User-Defined Functions , B035-1210 .
Window aggregate UDFs, see "Window Aggregate UDF" in
Teradata Vantage™ - SQL Operators and User-Defined Functions , B035-1210 .