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
YEARS_BETWEEN	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

Parent topic:SQL Functions and Operators

DECIMAL

Data Type Casting

SQL String Constants

CREATE EXTERNAL FORMAT

CREATE TABLE

GRANT

SELECT

GROUP BY Clause

Subqueries

SHOW

SQL Conditions

String Functions

ENCRYPT_KS

Pattern Matching

SQL Operators and Pattern Matching Functions

Regular Expression Details

Datetime Functions

Mathematical Functions

Aggregate Functions

Window Functions

Conditional Expressions

Formatting Functions

Type-Safe Casting Functions

JSON Functions

System Functions

Network Address Functions

Supported Functions and Return Types

Notes

GROUP BY Clause

Subqueries

ENCRYPT_KS

SQL Operators and Pattern Matching Functions

Regular Expression Details

Supported Functions and Return Types ​

Notes ​

Supported Functions and Return Types

Notes