SQL Functions

The API interprets all SQL function inputs as either strings or column identifiers. All literals must be escaped as strings with single quotes. For example, contrast the SQL Server syntax and provider syntax for the DATENAME function:

SQL Server:

SELECT DATENAME(yy,GETDATE())

Connect AI:

SELECT DATENAME('yy',GETDATE())

STRING Functions

String functions perform string manipulations and return a string value. See STRING Functions for more details.

SELECT CONCAT(firstname, space(4), lastname) FROM Account WHERE Industry = 'Floppy Disks'

DATE Functions

These functions perform date and date time manipulations. See DATE Functions for more details.

SELECT CURRENT_TIMESTAMP() FROM Account

Math Functions

These functions provide mathematical operations. See MATH Functions for more details.

SELECT RAND() FROM Account

Aggregate Functions

Aggregate functions return data from across multiple rows. See Aggregate Functions for more details.

SELECT MIN(AnnualRevenue), Name FROM Account WHERE Industry = 'Floppy Disks' GROUP BY Name

Window Functions

Window functions allow you to create computed fields from a group of rows (a window) that return a result for each row, as opposed to one computed result for a set of rows, as is the case with aggregate functions.

SELECT Name, Role, Earnings, COUNT() OVER (PARTITION BY Role) FROM Employees

Table-Valued Functions

Table-valued functions are functions that return a table (rowset).

SELECT A.ID, X.name FROM [TableWithXMLField] A CROSS APPLY XMLTABLE(A.XMLContent,'//*/item') WITH (name VARCHAR(255)) AS X

Function Parameters and Nesting SQL Functions

The provider supports column names, constants, and results of other functions as parameters to functions. The following are all valid uses of SQL functions:

SELECT CONCAT('Mr.', SPACE(2), firstname, SPACE(4), lastname) FROM Account

Overview

DDL Commands

SQL Commands

SQL Functions

DV Engine SQL Reference

SQL Functions

STRING Functions

DATE Functions

Math Functions

Aggregate Functions

Window Functions

Table-Valued Functions

Function Parameters and Nesting SQL Functions

​STRING Functions

​DATE Functions

​Math Functions

​Aggregate Functions

​Window Functions

​Table-Valued Functions

​Function Parameters and Nesting SQL Functions

STRING Functions

DATE Functions

Math Functions

Aggregate Functions

Window Functions

Table-Valued Functions

Function Parameters and Nesting SQL Functions