Supported Functions and Return Types

This topic contains an alphabetical list of supported functions, their return types, and function category. See the linked sections for more details and examples of each function.

Function Return Type Category
ABS SMALLINT Mathematical
ACOS DOUBLE Mathematical
ADD_MONTHS Depends on input type Datetime
AGE INTERVAL Datetime
ASCII INTEGER String
ASIN DOUBLE Mathematical
AVG Depends on input type Aggregate, Window
BIT_LENGTH INTEGER String
BTRIM VARCHAR String
CASE Depends on input type Conditional Expression
CBRT DOUBLE Mathematical
CEIL DOUBLE Mathematical
CHAR_LENGTH INTEGER String
CHR VARCHAR String
COALESCE Depends on input type Conditional Expression
CONCAT VARCHAR String
CONCAT_WS VARCHAR String
COS DOUBLE Mathematical
CONTAINS BOOLEAN Network Address
COUNT BIGINT Aggregate, Window
COUNT(DISTINCT) BIGINT Aggregate
CUME_DIST DOUBLE Window
CURRENT_DATABASE() NAME System
CURRENT_DATE DATE Datetime
CURRENT_QUERY() VARCHAR System
CURRENT_SCHEMA NAME System
CURRENT_SCHEMAS() NAME System
CURRENT_SETTING VARCHAR System
CURRENT_TIMESTAMP TIMESTAMPTZ Datetime
CURRENT_USER NAME System
CURRENT_UTC_TIMESTAMP() TIMESTAMP Datetime
DATEADD TIMESTAMP Datetime
DATEDIFF DOUBLE Datetime
DATENAME VARCHAR Datetime
DATE_PART DOUBLE Datetime
DATE_TRUNC TIMESTAMP Datetime
DAY DOUBLE Datetime
DAYS_BETWEEN DOUBLE Datetime
DECODE Depends on input type Conditional Expression
DECRYPT VARCHAR String
DECRYPT_KS VARCHAR String
DEGREES DOUBLE Mathematical
DENSE_RANK BIGINT Window
DIV DECIMAL Mathematical
ENCODE VARCHAR(64000) String
ENCRYPT VARCHAR String
ENCRYPT_KS VARCHAR String
ERF DOUBLE Mathematical
EXP DOUBLE Mathematical
EXTRACT DOUBLE Datetime
FIRST_VALUE Same as input type Window
FLOAT4, FLOAT8 FLOAT4, FLOAT8 Formatting
FLOOR DOUBLE Mathematical
GETBIT SMALLINT Bit Manipulation
GETDATE TIMESTAMP Datetime
GETDATABASEENCODING() NAME System
GREATEST and LEAST Depends on input type Conditional Expression
GROUP_CONCAT VARCHAR Aggregate
GROUPING INTEGER Aggregate
HAS_DATABASE_PRIVILEGE BOOLEAN System Information
HAS_FUNCTION_PRIVILEGE BOOLEAN System Information
HAS_KEY_PRIVILEGE BOOLEAN System Information
HAS_ROLE_PRIVILEGE BOOLEAN System Information
HAS_SCHEMA_PRIVILEGE BOOLEAN System Information
HAS_SYSTEM_PRIVILEGE BOOLEAN System Information
HAS_TABLE_PRIVILEGE BOOLEAN System Information
HASH VARCHAR(128) String
HASH4 INTEGER String
HASH8 BIGINT String
HMAC VARCHAR(128) String
HMAC_KS VARCHAR(128) String
IIF Depends on input type Conditional Expression
INITCAP VARCHAR String
INSTR INTEGER String
INT2, INT4, INT8 INT2, INT4, INT8 Formatting
INV_NORM DOUBLE Mathematical
IPV4_GET_INET, IPV6_GET_INET IPV4, IPV6 Network Address
IPV4_GET_MASKBITS, IPV6_GET_MASKBITS INTEGER Network Address
IPV4_HOSTMASK, IPV6_HOSTMASK IPV4, IPV6 Network Address
IPV4_INET_MERGE, IPV6_INET_MERGE VARCHAR Network Address
IPV4_NETMASK, IPV6_NETMASK IPV4, IPV6 Network Address
ISNULL Depends on input type Conditional Expression
JSON_ARRAY_STR VARCHAR JSON
JSON_INGEST VARCHAR JSON
JSON_LOOKUP VARCHAR JSON
JSON_OBJECT_STR VARCHAR JSON
JUSTIFY_DAYS INTERVAL Datetime
JUSTIFY_HOURS INTERVAL Datetime
JUSTIFY_INTERVAL INTERVAL Datetime
LAG Same as input type Window
LAST_DAY DATE Datetime
LAST_VALUE Same as input type Window
LEAD Same as input type Window
LEFT VARCHAR String
LENGTH INTEGER String
LISTAGG VARCHAR Aggregate
LN DOUBLE Mathematical
LOCALTIME TIME Datetime
LOCALTIMESTAMP TIMESTAMP Datetime
LOG DOUBLE or DECIMAL Mathematical
LOWER VARCHAR String
LPAD VARCHAR String
LTRIM VARCHAR String
MACADDR8_SET7BIT MACADDR8 Network Address
MAKE_DATE DATE Datetime
MAKE_INTERVAL INTERVAL Datetime
MAKE_TIME TIME Datetime
MAKE_TIMESTAMP TIMESTAMP Datetime
MAKE_TIMESTAMPTZ TIMESTAMPTZ Datetime
MAX Depends on input type Aggregate, Window
MD5 VARCHAR String
MEDIAN() DOUBLE Aggregate
MIN Depends on input type Aggregate, Window
MOD Depends on input type Mathematical
MONTH DOUBLE Datetime
MONTHS_BETWEEN DOUBLE Datetime
NEXT_DAY DATE Datetime
NEXTVAL BIGINT Sequence
NOW() TIMESTAMPTZ Datetime
NTILE BIGINT String
NULLIF Depends on input type Conditional Expression
NVL Depends on input type Conditional Expression
NVL2 Depends on input type Conditional Expression
NYSIIS VARCHAR(6) String
OCTET_LENGTH INTEGER String
OVERLAPS BOOLEAN Datetime
PERCENT_RANK DOUBLE Window
PERCENTILE_CONT DOUBLE Aggregate, Window
PERCENTILE_DISC Depends on the ORDER BY expression Aggregate, Window
POSITION INTEGER String
POWER DOUBLE or DECIMAL Mathematical
PROBNORM DECIMAL Mathematical
RANDOM() DOUBLE Mathematical
RANK BIGINT Window
REPEAT CHAR or VARCHAR String
REPLACE VARCHAR String
REVERSE VARCHAR String
RIGHT VARCHAR String
ROUND DECIMAL Mathematical
ROUND_VAR DECIMAL Mathematical
ROW_NUMBER BIGINT Window
RPAD VARCHAR String
RTRIM VARCHAR String
SAFE_TO_DATE DATE Type-Safe Compatibility
SAFE_TO_DOUBLE DOUBLE Type-Safe Compatibility
SAFE_TO_TIMESTAMP TIMESTAMP Type-Safe Compatibility
SESSION_USER NAME System Information
SIGN DOUBLE Mathematical
SIN DOUBLE Mathematical
SPLIT_PART VARCHAR String
SQRT DOUBLE or DECIMAL Mathematical
STDDEV_POP DOUBLE or DECIMAL Aggregate
STDDEV_SAMP DOUBLE or DECIMAL Aggregate
STRING_AGG VARCHAR Aggregate
STRPOS INTEGER String
SUBSTR The data type of the first argument to the function. String
SUBSTRING VARCHAR String, Pattern Matching
SUM Depends on input type Aggregate, Window
SYS.GEN_RANDOM_UUID() UUID System
TEXT (IPV4 or IPV6) VARCHAR Network Address
TIMEOFDAY() VARCHAR Datetime
TIMEZONE TIMESTAMPTZ Datetime
TO_ASCII VARCHAR String
TO_CHAR VARCHAR Formatting
TO_DATE DATE Formatting
TO_HEX VARCHAR String
TO_NUMBER DECIMAL Formatting
TO_TIMESTAMP TIMESTAMPTZ Formatting
TRANSLATE VARCHAR String
TRIM VARCHAR String
TRUNC DECIMAL Mathematical
TRUNC MACADDR, MACADDR8 Network Address
UPPER VARCHAR String
VAR_POP DECIMAL or DOUBLE Aggregate
VAR_SAMP DECIMAL or DOUBLE Aggregate
VERSION() VARCHAR System
YB_TERMINATE_SESSION BOOLEAN System
YEAR DOUBLE Datetime

Notes

The following data types are not supported as table column types; you may need to use explicit casts if functions or expressions return these types:
  • TEXT
  • NAME
  • INTERVAL
  • TIMETZ