Lyftrondata SQL functions
Lyftrondata ships with a concrete set of supported functions. They are based on SQL Server functions thus a name and behavior are the same in most cases. Using this reference, you can find all detailed descriptions and examples about the built-in functions.
Functions in Lyftrondata are categorized like the following:
Aggregate
| Function | Description |
|---|---|
| APPROX_COUNT_DISTINCT | Returns the number of rows in a data set that have distinct non-NULL values, where the return value is within a specified range of error tolerance. APPROX_COUNT_DISTINCT can return a value between 0 and 247. |
| AVG | Returns the average of the values in a group. Null values are ignored. |
| CHECKSUM_AGG | Returns the checksum of the values in a group. Null values are ignored. Can be followed by the OVER clause. |
| COUNT_BIG | Returns the number of items in a group. COUNT_BIG works like the COUNT function. The only difference between the two functions is their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value. |
| COUNT | Returns the number of items in a group. COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value. |
| GROUPING | Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified. |
| GROUPING_ID | Is a function that computes the level of grouping. GROUPING_ID can be used only in the SELECT <select> list, HAVING, or ORDER BY clauses when GROUP BY is specified. |
| KURTOSIS | Returns the kurtosis of the values in the expression. |
| MAX | Returns the maximum value in the expression. |
| MIN | Returns the minimum value in the expression. May be followed by the OVER clause. |
| STDEV | Returns the statistical standard deviation of all values in the specified expression. |
| STDEVP | Returns the statistical standard deviation for the population for all values in the specified expression. |
| SKEWNESS | Returns the skewness of the values in the expression. |
| SUM | Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored. |
| VAR | Returns the statistical variance of all values in the specified expression. May be followed by the OVER clause. |
| VARP | Returns the statistical variance for the population for all values in the specified expression. |
Analytical
| Function | Description |
|---|---|
| CUME_DIST | Calculates the cumulative distribution of a value in a group of values. That is, CUME_DIST computes the relative position of a specified value in a group of values. For a row r, assuming ascending ordering, the CUME_DIST of r is the number of rows with values lower than or equal to the value of r, divided by the number of rows evaluated in the partition or query result set. CUME_DIST is similar to the PERCENT_RANK function. |
| FIRST_VALUE | Returns the first value in an ordered set of values. |
| LAG | Accesses data from a previous row in the same result set without the use of a self-join. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row. |
| LAST_VALUE | Returns the last value in an ordered set of values. |
| LEAD | Accesses data from a subsequent row in the same result set without the use of a self-join. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row. |
| PERCENTILE_CONT | Calculates a percentile based on a continuous distribution of the column value. The result is interpolated and might not be equal to any of the specific values in the column. |
| PERCENTILE_DISC | Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset. For a given percentile value P, PERCENTILE_DISC sorts the values of the expression in the ORDER BY clause and returns the value with the smallest CUME_DIST value (with respect to the same sort specification) that is greater than or equal to P. For example, PERCENTILE_DISC (0.5) will compute the 50th percentile (that is, the median) of an expression. PERCENTILE_DISC calculates the percentile based on a discrete distribution of the column values; the result is equal to a specific value in the column. |
| PERCENT_RANK | Calculates the relative rank of a row within a group of rows. Use PERCENT_RANK to evaluate the relative standing of a value within a query result set or partition. PERCENT_RANK is similar to the CUME_DIST function. |
Azure Cognitive AI
| Function | Description |
|---|---|
| DETECT_FACES | Detects one or more human faces in an image and gets back face rectangles for where in the image the faces are, along with face attributes which contain machine learning-based predictions of facial features. |
| USER_TO_ITEM_RECOMMENDATION | Learns from previous transactions to predict which items are more likely to be of interest to or purchased by your customers. |
Date & time
| Function | Description |
|---|---|
| DATEADD | Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date. |
| DATEDIFF | Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate. |
| DATEFROMPARTS | Returns a date value for the specified year, month, and day. |
| DATENAME | Returns a character string that represents the specified datepart of the specified date. |
| DATEPART | Returns an integer that represents the specified datepart of the specified date. |
| DATETIME2FROMPARTS | Returns a datetime2 value for the specified date and time and with the specified precision. |
| DATETIMEFROMPARTS | Returns a datetime value for the specified date and time. |
| DATETIMEOFFSETFROMPARTS | NOT SUPPORTED. |
| DAY | Returns an integer representing the day (day of the month) of the specified date. |
| EOMONTH | Returns the last day of the month that contains the specified date, with an optional offset. |
| GETDATE | Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of Lyftrondata is running. |
| GETUTCDATE | Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of Lyftrondata is running. |
| ISDATE | Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0. ISDATE returns 0 if the expression is a datetime2 value. |
| MONTH | Returns an integer that represents the month of the specified date. |
| SMALLDATETIMEFROMPARTS | Returns a smalldatetime value for the specified date and time. |
| SWITCHOFFSET | NOT SUPPORTED. |
| SYSDATETIME | Returns a datetime2(7) value that contains the date and time of the computer on which the instance of Lyftrondata is running. |
| SYSDATETIMEOFFSET | NOT SUPPORTED. |
| SYSUTCDATETIME | Returns a datetime2 value that contains the date and time of the computer on which the instance of Lyftrondata is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits. |
| TIMEFROMPARTS | Returns a time value for the specified time and with the specified precision. |
| TODATETIMEOFFSET | NOT SUPPORTED. |
| YEAR | Returns an integer that represents the year of the specified date. |
Expression
| Function | Description |
|---|---|
| COALESCE | Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. |
| IIF | Returns one of two values, depending on whether the Boolean expression evaluates to true or false. |
| ISNULL | Replaces NULL with the specified replacement value. |
| NULLIF | Returns a null value if the two specified expressions are equal. |
Math
| Function | Description |
|---|---|
| ABS | A mathematical function that returns the absolute (positive) value of the specified numeric expression. (ABS changes negative values to positive values. ABS has no effect on zero or positive values.) |
| ACOS | A mathematical function that returns the angle, in radians, whose cosine is the specified float expression; also called arc cosine. |
| ASIN | Returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine. |
| ATAN | Returns the angle in radians whose tangent is a specified float expression. This is also called arctangent. |
| ATN2 | Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions. |
| CEILING | Returns the smallest integer greater than, or equal to, the specified numeric expression. |
| COS | Is a mathematical function that returns the trigonometric cosine of the specified angle, in radians, in the specified expression. |
| COT | A mathematical function that returns the trigonometric cotangent of the specified angle, in radians, in the specified float expression. |
| DEGREES | Returns the corresponding angle in degrees for an angle specified in radians. |
| EXP | Returns the exponential value of the specified float expression. |
| FLOOR | Returns the largest integer less than or equal to the specified numeric expression. |
| LOG10 | Returns the base-10 logarithm of the specified float expression. |
| LOG | Returns the natural logarithm of the specified float expression. |
| PI | Returns the constant value of PI. |
| POWER | Returns the value of the specified expression to the specified power. |
| RADIANS | Returns radians when a numeric expression, in degrees, is entered. |
| RAND | Returns a pseudo-random float value from 0 through 1, exclusive. |
| ROUND | Returns a numeric value, rounded to the specified length or precision. |
| SIGN | Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression. |
| SIN | Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression. |
| SQRT | Returns the square root of the specified float value. |
| SQUARE | Returns the square of the specified float value. |
| TAN | Returns the tangent of the input expression. |
Metadata
| Function | Description |
|---|---|
| QUA_PARTITION_HINT | Loads partitioning information. Should be used as a constraint of a table. |
Ranking
| Function | Description |
|---|---|
| DENSE_RANK | Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. |
| NTILE | Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs. |
| RANK | Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. |
| ROW_NUMBER | Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. |
String
| Function | Description |
|---|---|
| ASCII | Returns the ASCII code value of the leftmost character of a character expression. |
| CHAR | Converts an int ASCII code to a character. |
| CHARINDEX | Searches an expression for another expression and returns its starting position if found. |
| CONCAT | Returns a string that is the result of concatenating two or more string values. |
| FORMAT | Returns a value formatted with the specified format and optional culture. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. |
| LEFT | Returns the left part of a character string with the specified number of characters. |
| LEN | Returns the number of characters of the specified string expression, excluding trailing blanks. |
| LEVENSHTEIN | Returns the Levenshtein distance between the two given strings. |
| LOWER | Returns a character expression after converting uppercase character data to lowercase. |
| LTRIM | Returns a character expression after it removes leading blanks. |
| NCHAR | Returns the Unicode character with the specified integer code, as defined by the Unicode standard. |
| PATINDEX | Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. |
| QUOTENAME | Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. |
| REPLACE | Replaces all occurrences of a specified string value with another string value. |
| REPLICATE | Repeats a string value a specified number of times. |
| REVERSE | Returns the reverse order of a string value. |
| RIGHT | Returns the right part of a character string with the specified number of characters. |
| RTRIM | Returns a character string after truncating all trailing spaces. |
| SPACE | Returns a string of repeated spaces. |
| SPLIT | Splits a string around a java-style regular expression. |
| STR | Returns character data converted from numeric data. |
| STUFF | The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. |
| SUBSTRING | Returns part of a character, binary, text, or image expression. |
| UNICODE | Returns the integer value, as defined by the Unicode standard, for the first character of the input expression. |
| UPPER | Returns a character expression with lowercase character data converted to uppercase. |
System
| Function | Description |
|---|---|
| @@DATEFIRST | Returns the current value, for a session, of SET DATEFIRST. |
| @@MAX_PRECISION | Returns the precision level used by decimal and numeric data types as currently set in Lyftron. |
| @@MICROSOFTVERSION | Returns version information of SQL Server which Lyftrondata is compatible with. |
| @@OPTIONS | Returns information about the current SET options. |
| @@SERVICENAME | Returns the name of the registry key under which Lyftrondata is running. @@SERVICENAME returns 'SQL2012'. |
| @@SPID | Returns the session ID of the current user process. |
| @@TRANCOUNT | Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection. |
| @@VERSION | Returns system and build information for the current installation of Lyftrondata. |
| APP_NAME | Returns the application name for the current session if set by the application. |
| ASSEMBLYPROPERTY | Returns information about a property of an assembly. |
| COLLATIONNAME | Returns collation name by collation ID. |
| COLLATIONPROPERTY | Returns the property of a specified collation. |
| COLLATIONPROPERTYFROMID | Returns the property of a specified collation. |
| COL_LENGTH | Returns the defined length, in bytes, of a column. |
| COL_NAME | Returns the name of a column from a specified corresponding table identification number and column identification number. |
| COLUMNPROPERTY | Returns information about a column or parameter. |
| CURRENT_USER | Returns the name of the current user. This function is equivalent to USER_NAME(). |
| DATABASE_PRINCIPAL_ID | Returns the ID number of a principal in the current database. |
| DATABASEPROPERTYEX | Returns the current setting of the specified database option or property for the specified database in Lyftron. |
| DB_ID | Returns the database identification (ID) number. |
| DB_NAME | Returns the database name. |
| FULLTEXTCATALOGPROPERTY | Returns information about full-text catalog properties. |
| FULLTEXTSERVICEPROPERTY | Returns information related to the properties of the Full-Text Engine. |
| HAS_DBACCESS | Returns information about whether the user has access to the specified database. |
| HASHBYTES | Returns the MD2, MD4, MD5, SHA, SHA1, or SHA2 hash of its input. |
| HAS_PERMS_BY_NAME | Evaluates the effective permission of the current user on a securable. |
| INDEXPROPERTY | Returns the named index or statistics property value of a specified table identification number, index or statistics name, and property name. Returns NULL for XML indexes. |
| IS_MEMBER | Indicates whether the current user is a member of the specified Microsoft Windows group or Lyftron database role. |
| IS_ROLEMEMBER | Indicates whether a specified database principle is a member of the specified database role. |
| IS_SRVROLEMEMBER | Indicates whether a SQL Server login is a member of the specified server role. |
| LOAD_FILE | Loads file content in bytes. |
| NEWID | Creates a unique value of type uniqueidentifier. |
| OBJECT_ID | Returns the database object identification number of a schema-scoped object. |
| OBJECT_NAME | Returns the database object name for schema-scoped objects. |
| OBJECTPROPERTYEX | Returns information about schema-scoped objects in the current database. OBJECTPROPERTYEX cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications. |
| OBJECTPROPERTY | Returns information about schema-scoped objects in the current database. OBJECTPROPERTY cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications. |
| OBJECT_SCHEMA_NAME | Returns the database schema name for schema-scoped objects. |
| PARSENAME | Returns the specified part of an object name. The parts of an object that can be retrieved are the object name, owner name, database name, and server name. |
| QUA_GET_INCREMENTAL_MARKER | Returns a marker value for a last updated row in a table. |
| SCHEMA_ID | Returns the schema ID associated with a schema name. |
| SCHEMA_NAME | Returns the schema name associated with a schema ID. |
| SERVERPROPERTY | Returns property information about the server instance. |
| SESSIONPROPERTY | Returns the SET options settings of a session. |
| SUSER_SNAME | Returns the login identification name of the user. |
| SYSTEM_USER | Allows a system-supplied value for the current login to be inserted into a table when no default value is specified. |
| TYPE_ID | Returns the ID for a specified data type name. |
| TYPE_NAME | Returns the unqualified type name of a specified type ID. |
| TYPEPROPERTY | Returns information about a data type. |
| USER_NAME | Returns a database user name from a specified identification number. |
Data Parsing
| Function | Description |
|---|---|
| JSON_VALUE | Returns the content of the item queried by XPath expression. |
| XML Function | Returns the content of the item queried by XML parsing expression. |
Tabular
| Function | Description |
|---|---|
| fn_builtin_permissions | Returns a description of the built in permissions hierarchy of the server. fn_builtin_permissions returns all permissions. |
| fn_helpcollations | Returns a list of all collations. |
| $GetColumnList | Not supported. |
| $GetTableList | Not supported. |
| QUA_SHUFFLE | Allows data pseudonymization from an expression or from a column. |
| QUA_DESHUFFLE | Deanonymizes data by doing the opposite operation to QUA_SHUFFLE. |