Miscellaneous Functions

Advantage SQL Engine

APPLICATIONID( )

Returns the Application ID of the connection application. The Application ID is initialized to the file name of the application (executable) that connected to the Advantage Database Server (only for ACE based clients). The Application ID can be set and retrieved using the sp_SetApplicationID and sp_GetApplicationID system procedures.

CAST ( expr AS data-type [( precision [, scale ] ) ] )

Returns the value of expr casted to data-type with an optional precision and scale value. The data-type parameter can be SQL_BINARY, SQL_VARBINARY, SQL_BIT (logical), SQL_VARCHAR, SQL_CHAR, SQL_DATE, SQL_NUMERIC, SQL_DOUBLE, SQL_INTEGER, SQL_TIMESTAMP, SQL_TIME, or SQL_MONEY. Only SQL_BINARY, SQL_VARBINARY, SQL_VARCHAR, SQL_CHAR, and SQL_NUMERIC support the precision parameter and only SQL_NUMERIC supports the scale parameter.

COALESCE( expr1, ... exprn )

Returns the first non-NULL expression result. All expressions must be of the same type or be implicitly convertible to the same type. No parameters or BLOBs are allowed as an expression type. If all the expressions evaluate to NULL, COALESCE returns a NULL value.

CONTAINS( field | *, condition )

Performs a full text search on the given field or on all fields that have full text search (FTS) indexes if the asterisk (*) is specified. This function returns a logical TRUE or FALSE for records that meet the search condition. For details, see Full Text Search. The search condition (second parameter) is given as a character string. It can be any valid character value (including a character field value or expression result), but it must be a character literal or a parameter value in order to be fully optimized.

CONVERT( expr, data-type )

Returns the value of expr converted to data-type. The data-type parameter can be SQL_BINARY, SQL_VARBINARY, SQL_BIT (logical), SQL_VARCHAR, SQL_CHAR, SQL_DATE, SQL_DOUBLE, SQL_INTEGER, SQL_NUMERIC, SQL_TIME, SQL_TIMESTAMP, or SQL_MONEY.

DATABASE( )

Returns name of database for the connection.

DIFFERENCE( str1, str2 )

Returns an integer value that indicates the difference between the values returned by the SOUNDEX() function for str1 and str2. The value returned will be in the range 0 to 4. The value 4 indicates the closest match (it actually means that the soundex encoding of str1 and str2 are identical). The value 0 indicates the lowest possible phonetic match, with the values 1, 2, and 3 indicating increasing degrees of phonetic matching.

IFNULL( expr, value )

If expr is NULL, value is returned. If expr is not NULL, expr is returned.

IIF(boolean_expr, true_expr, false_expr)

If boolean_expr is True, the evaluation of true_expr is returned. If boolean_expr is False, the evaluation of false_expr is returned. See IIF for more information.

ISNULL( expr, value )

If expr is NULL, value is returned. If expr is not NULL, expr is returned.

LASTAUTOINC( CONNECTION | STATEMENT )

Returns the last used autoinc value from an insert or append. Specifying CONNECTION will return the last used value for the entire connection. Specifying STATEMENT returns the last used value for only the current SQL statement. If no autoinc value has been updated yet, a NULL value is returned.

Note Triggers that operate on tables with autoinc fields may affect the last autoinc value.

Note SQL script triggers run on their own SQL statement. Therefore, calling LASTAUTOINC( STATEMENT ) inside an SQL script trigger would return the lastautoinc value used by the trigger's SQL statement, not the original SQL statement which caused the trigger to fire. To obtain the original SQL statement's lastautoinc value, use LASTAUTOINC( CONNECTION ) instead.

Example SELECT LASTAUTOINC( STATEMENT ) FROM system.iota

NEWIDSTRING

Returns a GUID formatted as a string. If the format parameter is not specified, the GUID string is formatted as a hexadecimal string with the following pattern xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. The format parameter can be the following values:

M or MIME A GUID encoded as a 24-byte string using MIME base64 encoding with the format of xxxxxxxxxxxxxxxxxxxxxxxx. Base64 encoded strings are case sensitive and should not be stored in case insensitive string fields.

F or File A GUID encoded as a 22-byte string using File and URL Safe base64 encoding with the format of xxxxxxxxxxxxxxxxxxxxxx. Base64 encoded strings are case sensitive and should not be stored in case insensitive string fields.

N or Numbers A GUID encoded as a 32-byte hexadecimal string value with a format of xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.

D or Delimited A GUID encoded as a 32-byte hexadecimal string with a format of xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.

B or Bracketed A GUID encoded as a 32-byte hexadecimal string with a format of {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}

P or Parenthesis A GUID encoded as a 32-byte hexadecimal string with a format of (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)

Note This function is currently only supported on Microsoft Windows operating systems.

SCORE( field | *, condition )

This returns an integer value representing the score of the search condition for a given record. The score is the total count of the words from the search condition that are in the field. This is normally expected to be used in conjunction with the CONTAINS() scalar function (e.g., in ORDER BY clauses), but it can used in statements that do not use the CONTAINS() function.

SCORE( n )

This is an alternative version of the SCORE() function. The integer parameter n refers to the nth instance of CONTAINS() in the SQL statement.

SCOREDISTINCT( field | *, condition )

This scoring function returns the count of the words in the search condition that appear one or more times in the field for a given record. For example, if the search condition contains three words, this function will return a value from 0 to 3.

SCOREDISTINCT( n )

This is an alternative version of the SCOREDISTINCT() function. The integer parameter n refers to the nth instance of CONTAINS() in the SQL statement.

SOUNDEX( str )

The soundex function returns a four-digit phonetic encoding of the given string. The encoding is of the form <letter><digit><digit><digit>.

USER( )

Returns the user name in the DBMS.