SQL Reference

Other Functions and Expressions

The following miscellaneous functions and expressions are supported.
CASE
CASE operates similar to conditional scripting in other programming languages, allowing it to function like an if / then / else statement or as a switch statement.  
CASE sets one or more WHEN conditions to be evaluated. When a condition is evaluated as true, the database applies the specified action in a THEN clause. If no matches are found, the database applies the action in the ELSE clause. If no ELSE clause is specified and no matches are found, the result is NULL.
This evaluation creates a new column where the values are expressions inside the THEN clause.
Syntax
SQL
--To use CASE like an if / then statement: 

CASE <caseCondition> ELSE expression | NULL END

<caseCondition> ::=
WHEN search_condition | expression THEN expression | NULL

--To use CASE like a switch statement: 
CASE switch_expression <switchCondition> ELSE expression | NULL END

<switchCondition> ::=
WHEN switch_value THEN expression | NULL
--To use CASE like an if / then statement: 

CASE <caseCondition> ELSE expression | NULL END

<caseCondition> ::=
WHEN search_condition | expression THEN expression | NULL

--To use CASE like a switch statement: 
CASE switch_expression <switchCondition> ELSE expression | NULL END

<switchCondition> ::=
WHEN switch_value THEN expression | NULL
﻿
Arguments
Description
search_condition
An condition including a column name and a Boolean expression to evaluate.  
switch_expression
A column name to be used to evaluate a switch_value. 
switch_value
A value to be evaluated for the switch_expression.
expression
An expression to return based on evaluating the search_condition or switch_expression.
Example
This example uses conditional logic with CASE to categorize individuals into different groups based on their ages. 
SQL
SELECT age, 
  CASE 
    WHEN age < 13 THEN 'child'
    WHEN age < 19 THEN 'teen'
    ELSE 'adult'
  END AS age_grp
FROM sys.people
ORDER BY age;
SELECT age, 
  CASE 
    WHEN age < 13 THEN 'child'
    WHEN age < 19 THEN 'teen'
    ELSE 'adult'
  END AS age_grp
FROM sys.people
ORDER BY age;
﻿
Example
This example uses switch conditions to categorize age groups. 
SQL
SELECT age,
  CASE age
    WHEN 13 THEN 'thirteen'
    WHEN 14 THEN 'fourteen'
    WHEN 15 THEN 'fifteen'
    WHEN 16 THEN 'sixteen'
    WHEN 17 THEN 'seventeen'
    WHEN 18 THEN 'eighteen'
    WHEN 19 THEN 'nineteen'
    ELSE 'not teens'
  END
FROM sys.people;
SELECT age,
  CASE age
    WHEN 13 THEN 'thirteen'
    WHEN 14 THEN 'fourteen'
    WHEN 15 THEN 'fifteen'
    WHEN 16 THEN 'sixteen'
    WHEN 17 THEN 'seventeen'
    WHEN 18 THEN 'eighteen'
    WHEN 19 THEN 'nineteen'
    ELSE 'not teens'
  END
FROM sys.people;
﻿
COALESCE 
Returns the first argument that is not NULL, or NULL if all arguments are NULL. Arguments must be of the same type and the return value is the most compatible type.
COALESCE can work with mixed-type numeric arguments, which means the lower precedence types are cast to the highest one. In the case of using TIMESTAMP and DATE data types, all values are cast to a timestamp.
Syntax
SQL
COALESCE(value1, value2 [,...])
COALESCE(value1, value2 [,...])
﻿
Argument
Data Type
Description
value [,...]
All data types are supported. 
All values must be of the same type. 
Values for comparison to find the value that is not NULL. 
Example
SQL
SELECT COALESCE(null,null,13,null,8);
SELECT COALESCE(null,null,13,null,8);
﻿
Output: 13
COMMIT
Returns the most recent commit hash of the database to which the client is currently connected.
Syntax
SQL
COMMIT()
COMMIT()
﻿
Example
SQL
SELECT COMMIT();
SELECT COMMIT();
﻿
Output: cd0d53e3aa
CURRENT_DATABASE
Alias for DATABASE﻿.  
CURRENT_NODE
Returns the name of the SQL Node where the current query executes. The name of the node corresponds to the name column in the sys.nodes system catalog table.
Syntax
SQL
CURRENT_NODE() 
CURRENT_NODE() 
﻿
Example
SQL
SELECT CURRENT_NODE();
SELECT CURRENT_NODE();
﻿
Output: sql0
CURRENT_NODE_ID
Returns the identifier of the SQL Node where the current query executes. The name of the node corresponds to the id column in the sys.nodes system catalog table.
Syntax
SQL
CURRENT_NODE_ID() 
CURRENT_NODE_ID() 
﻿
Example
SQL
SELECT CURRENT_NODE_ID();
SELECT CURRENT_NODE_ID();
﻿
Output: f690c14a-4e4f-4143-8e5e-20c90b60e15c
CURRENT_SCHEMA
Returns the name of the current schema.
Syntax
SQL
CURRENT_SCHEMA() 
CURRENT_SCHEMA() 
﻿
Example
SQL
SELECT CURRENT_SCHEMA();
SELECT CURRENT_SCHEMA();
﻿
Output: schema@database 
CURRENT_SYSTEM
Returns the name of the system. You can set the name using the ALTER SYSTEM RENAME TO﻿ SQL statement.
If you never set the system name, the 
﻿ system initializes the name of the system as a random identifier (UUID).
Syntax
SQL
CURRENT_SYSTEM() 
CURRENT_SYSTEM() 
﻿
Example
SQL
SELECT CURRENT_SYSTEM();
SELECT CURRENT_SYSTEM();
﻿
Output: 2677b88c-7f07-470c-be3e-e1bafddc1221
CURRENT_USER
Returns the user for the current connection.
Syntax
SQL
CURRENT_USER() 
CURRENT_USER() 
﻿
Example
SQL
SELECT CURRENT_USER();
SELECT CURRENT_USER();
﻿
Output: user@database
DATABASE
Alias for CURRENT_DATABASE.
Returns the name of the database to which the client is currently connected.
Syntax
SQL
DATABASE() 
DATABASE() 
﻿
Example
SQL
SELECT DATABASE();
SELECT DATABASE();
﻿
Output: test
GREATEST
Returns the largest non-NULL value of all the arguments, or NULL if all the arguments are NULL. All values must be comparable to each other.
Syntax
SQL
GREATEST(value [,...])
GREATEST(value [,...])
﻿
Argument
Data Type
Description
value [,...]
All data types are supported. 
﻿
All values must either be of the same type or comparable to each other. 
A series of values to compare to find the largest value of the set. 
Example
SQL
SELECT GREATEST(12,13,5,8);
SELECT GREATEST(12,13,5,8);
﻿
Output: 13
IF_NULL
Returns the first argument that is not NULL or NULL if all arguments are NULL. Arguments must be of the same type and the return value is the most compatible type.
IF_NULL can work with mixed-type numeric arguments, which means the lower precedence types are cast to the highest one. In the case of using TIMESTAMP and DATE data types, all values are cast to a timestamp.
Syntax
SQL
IF_NULL(value1, value2)
IF_NULL(value1, value2)
﻿
Argument
Data Type
Description
value [,...]
All data types are supported. 
All values must be of the same type. 
Values for comparison to find the value that is not NULL. 
Example
SQL
SELECT IF_NULL(null, 5)
SELECT IF_NULL(null, 5)
﻿
Output: 5
LEAST
Returns the smallest non-NULL value of all arguments, or NULL if all arguments are NULL. All values must be comparable to each other.
Syntax
SQL
LEAST(value [,...])
LEAST(value [,...])
﻿
Argument
Data Type
Description
value [,...]
All data types are supported. 
﻿
All values must either be of the same type or comparable to each other. 
A series of values to compare to find the smallest value of the set. 
Example
SQL
SELECT LEAST(12,13,5,8);
SELECT LEAST(12,13,5,8);
﻿
Output: 5
MURMUR3
Returns a 32-bit MurmurHash3 hash of the input value as an INTEGER data type. If you specify a NULL input value, the function returns 0. This function is primarily useful for partitioning data.
Syntax
SQL
MURMUR3(value)
MURMUR3(value)
﻿
Argument
Data Type
Description
value
All SQL data types are supported. 
Specified value for the MurmurHash3 hash function. 
Example
SQL
SELECT MURMUR3(null);
SELECT MURMUR3(null);
﻿
Output: 0
Example
SQL
SELECT MURMUR3('fred');
SELECT MURMUR3('fred');
﻿
Output: 331477181
NULL_IF
Returns the NULL value if two arguments are equal; otherwise returns the first argument.
Syntax
SQL
NULL_IF(value1, value2)
NULL_IF(value1, value2)
﻿
Example
SQL
SELECT NULL_IF(13,12);
SELECT NULL_IF(13,12);
﻿
Output: 13
Example
SQL
SELECT NULL_IF(13,13);
SELECT NULL_IF(13,13);
﻿
Output: NULL
SHOW
The SHOW function enables you to explore the database and its metadata for user-defined items. For details, see Built-In Views﻿.
Syntax
SQL
SHOW <item>;

-- <item> can be any of the following: 
{   COLUMNS, 
    DATA_TYPES, 
    DATABASES, 
    GROUPS, 
    INDEXES, 
    RESERVED_WORDS, 
    SCHEMATA, 
    TABLES,
    USERS, 
    VIEWS } 
SHOW <item>;

-- <item> can be any of the following: 
{   COLUMNS, 
    DATA_TYPES, 
    DATABASES, 
    GROUPS, 
    INDEXES, 
    RESERVED_WORDS, 
    SCHEMATA, 
    TABLES,
    USERS, 
    VIEWS } 
﻿
Examples
This example uses SHOW to explore databases on the system. 
SQL
SHOW DATABASES;
SHOW DATABASES;
﻿
Output
SQL
database_name 
created 
-------------------------------------------------------------------------- 
test 
2022-12-19 19:52:34.576426348
database_name 
created 
-------------------------------------------------------------------------- 
test 
2022-12-19 19:52:34.576426348
﻿
This example explores table data. 
SQL
SHOW TABLES; 
SHOW TABLES; 
﻿
Output
SQL
table_catalog 
table_schema 
table_name 
table_type 
is_insertable_into 
created_at
-------------------------------------------------------------------------- 
test 
test_schema 
test_table 
BASE TABLE 
YES 
2022-12-20 14:45:13.858144104
table_catalog 
table_schema 
table_name 
table_type 
is_insertable_into 
created_at
-------------------------------------------------------------------------- 
test 
test_schema 
test_table 
BASE TABLE 
YES 
2022-12-20 14:45:13.858144104
﻿
TYPE_STRIP
Returns the SQL type of the specified value. 
Syntax
SQL
TYPE_STRIP(value)
TYPE_STRIP(value)
﻿
Argument
Data Type
Description
value
All data types are supported. 
Specified value for the determination of the type. 
You can specify columns, literals, and expressions.
Examples
SQL
SELECT TYPE_STRIP(_R{1,2,3});
SELECT TYPE_STRIP(_R{1,2,3});
﻿
Output: MATRIX[1][3] 
SQL
SELECT TYPE_STRIP(current_user());
SELECT TYPE_STRIP(current_user());
﻿
Output: CHAR
SQL
SELECT TYPE_STRIP(NULL);
SELECT TYPE_STRIP(NULL);
﻿
Output: NULL 
SQL
SELECT TYPE_STRIP(NULL::INT[]);
SELECT TYPE_STRIP(NULL::INT[]);
﻿
Output: INT[] 
VERSION
Returns the version of the database to which the client is currently connected.
Syntax
SQL
VERSION()
VERSION()
﻿
Example
SQL
SELECT VERSION();
SELECT VERSION();
﻿
Output: 22.0.0-20230105.2353
ZN
If x is NULL, returns 0. Otherwise, returns x.
Syntax
SQL
ZN(x)
ZN(x)
﻿
Example
SQL
SELECT ZN(null);
SELECT ZN(null);
﻿
Output: 0
Example
SQL
SELECT ZN(12);
SELECT ZN(12);
﻿
Output: 12
Related Links
﻿Database, Tables, Views, and Indexes﻿﻿
﻿System Catalog Reference﻿﻿
﻿Database Administration﻿﻿

Arguments	Description
search_condition	An condition including a column name and a Boolean expression to evaluate.
switch_expression	A column name to be used to evaluate a switch_value.
switch_value	A value to be evaluated for the switch_expression.
expression	An expression to return based on evaluating the search_condition or switch_expression.

Argument	Data Type	Description
value [,...]	All data types are supported. All values must be of the same type.	Values for comparison to find the value that is not NULL.

Argument	Data Type	Description
value [,...]	All data types are supported. All values must either be of the same type or comparable to each other.	A series of values to compare to find the largest value of the set.

Argument	Data Type	Description
value [,...]	All data types are supported. All values must be of the same type.	Values for comparison to find the value that is not NULL.

Argument	Data Type	Description
value [,...]	All data types are supported. All values must either be of the same type or comparable to each other.	A series of values to compare to find the smallest value of the set.

Argument	Data Type	Description
value	All SQL data types are supported.	Specified value for the MurmurHash3 hash function.

Argument	Data Type	Description
value	All data types are supported.	Specified value for the determination of the type. You can specify columns, literals, and expressions.

Updated 22 May 2024

Did this page help you?

Window Aggregate Functions

Data Definition Language (DDL) Command Reference