List of SQL Extended Functions "A" to "S"
autocounter()
This expression is used to create ascending lists of numbers. It can be used to create mock data or simple, unique ascending values, although it is not guaranteed to be serial in nature or to start at any particular value.
Example: SELECT autocounter() from customers
Example: SELECT 1e10 -autocounter() * 100 from customers
Since autocounter() returns a number, it can be used in math expressions like any other function. Here we have selected 10 billion and subtracted autocounter() times 100 to create a descending value.
BIBXREF(field, prefix, index)
This is a function for a specific customer that scans the data in the record area defined by field looking for the n-th instance, as identified by index, of the field prefixed by the special character prefix. The range of possible prefixes is preset by the customer specific tables for which this function was implemented.
Example: SELECT BIBXREF(data, 241, 2) FROM MyTable
cnxclientcodepage()
This returns the CONNX codepage of the client operating system, including any override value in the sqlregisty or CONNXRegistry.
Example: SELECT cnxclientcodepage()
cnxclientdefaultcodepage()
This returns the default codepage of the client operating system as detected by CONNX.
Example: SELECT cnxclientdefaultcodepage()
cnxcurrentconnections()
Returns the current number of connections to data servers (includes JDBC server).
Example: SELECT cnxcurrentconnections()
cnxcurrentstatements()
Returns the current number of active SQL Statements on the data server (includes JDBC Server).
Example: SELECT cnxcurrentstatements()
CNXFORCEBINARY (string_exp)
This expression is useful in the manipulation of the BINARY data type as it forces the data type of the expression to convert to BINARY. No additional data type checking or data conversion is performed.
Example: SELECT CNXFORCEBINARY (CharacterField) from MyTable
The above syntax converts the column CharacterField to binary. It does not assume that the character field is a hexadecimal representation of a binary field.
CNXFORCECHAR (binary_exp)
This expression is useful in the manipulation of the BINARY data type as it forces the data type of the expression to convert to CHAR. No additional data type checking or data conversion is performed.
Example: SELECT CNXFORCECHAR (BinaryField) from MyTable
The above syntax converts the column BinaryField to character. It does not attempt to convert the binary field to a character hexadecimal representation.
cnxpeakconnections()
Returns the peak number of connections (high water mark) to data servers (includes JDBC server).
Example: SELECT cnxpeakconnections()
cnxpeakstatements()
Returns to peak number of simultaneous SQL Statements (high water mark) active on the data server (includes JDBC server).
Example: SELECT cnxpeakstatements()
cnxsetfilename()
This function changes the physical file name of the specified table or table alias for the rest of the duration of the connection.
The syntax of this function is as follows: cnxsetfilename ('table or alias name', 'New physical file name', refresh indexes flag)
Example: select cnxsetfilename ('uniquealias1', 'CNXDIR:datatypetest2_CNXTABLE.DAT', 1)
Example: select * from datatypetest2 uniquealias1
CNXSLEEP(numeric_exp)
This function is used to create a delay, where numeric_exp represents the number of milliseconds to wait.
Example: SELECT cnxsleep(5000) from customers
This example creates a pause of 5 seconds for every record in the customers_rms table.
cnxtotalconnections()
Returns the total number of connections to data servers over the life of the data servers (includes JDBC server).
Example: SELECT cnxtotalconnections()
cnxtotalstatements()
Returns the total number of SQL Statements created over the life of the data server (includes JDBC server).
Example: SELECT cnxtotalstatements()
ILIKE
The SQL primary command ILIKE, also known as the ILIKE operator, can be used for wildcard comparison against String values to filter specific data. The ILIKE operator has three special characters, each with a different usage: the percent sign (%), the underscore (_), and the caret (^).
The "I" in ILIKE represents an abbreviated form of the word "insensitive"; therefore, the ILIKE operator is NEVER case-sensitive (even if the database tables are case-sensitive).
% multi-character wildcardThe use of the percent sign (%) in a ILIKE clause retrieves matches of zero or more characters.
Example: If the column TEST contains 'ABCDEF' the expression TEST ILIKE '%F' returns TRUE, the expression TEST ILIKE '%C%' returns TRUE, and the expression TEST ILIKE '%G' returns TRUE.
Likewise, the expression TEST ILIKE 'f%' returns TRUE.
The Microsoft Jet Engine for Microsoft Access uses an asterisk (*) as the wildcard character instead of the percent sign (%). Replace the percent sign (%) wildcard with an asterisk (*) when using Microsoft Access, or when using other DAO-compliant applications.
_ single-character wildcard The use of an underscore (_) in a ILIKE clause retrieves matches of a single character.
Example: If the column TEST contains 'GHIJKLM' the expression TEST ILIKE 'G_IJKLM' returns TRUE, and the expression TEST ILIKE 'A_IJKLM' returns TRUE.
Likewise, the expression TEST ILIKE 'A_ijkLM' returns TRUE.
^ literal character identifier The use of the caret (^) in a ILIKE cause permits the use of any of the wildcard characters %, _ or ^. If the ^ character is placed in front of either of the other two wildcard characters, the characters following are treated as normal literals.
Example: If the column TEST contains '10% of the data' the expression TEST ILIKE '10^% of the data' returns TRUE, the expression TEST ILIKE '10%data' returns TRUE, and the expression TEST ILIKE '10data' returns FALSE.
SLIKE
The SQL primary command SLIKE, also known as the SLIKE operator, can be used for wildcard comparison against String values to filter specific data. The SLIKE operator has three special characters, each with a different usage: the percent sign (%), the underscore (_), and the caret (^).
The "S" in SLIKE represents an abbreviated form of the word "sensitive"; therefore, the SLIKE operator is ALWAYS case-sensitive (even if the database tables are NOT case-sensitive).
% multi-character wildcard The use of the percent sign (%) in a SLIKE clause retrieves matches of zero or more characters.
Example: If the column TEST contains 'ABCDEF' the expression TEST SLIKE '%F' returns TRUE, the expression TEST SLIKE '%C%' returns TRUE, and the expression TEST SLIKE '%G' returns FALSE.
Likewise, the expression TEST SLIKE 'f%' returns FALSE.
The Microsoft Jet Engine for Microsoft Access uses an asterisk (*) as the wildcard character instead of the percent sign (%). Replace the percent sign (%) wildcard with an asterisk (*) when using Microsoft Access, or when using other DAO-compliant applications.
_ single-character wildcard The use of an underscore (_) in a SLIKE clause retrieves matches of a single character.
Example: If the column TEST contains 'GHIJKLM' the expression TEST ILIKE 'G_IJKLM' returns TRUE, and the expression TEST ILIKE 'A_IJKLM' returns FALSE.
Likewise, the expression TEST ILIKE 'A_ijkLM' returns FALSE.
^ literal character identifier The use of the caret (^) in a SLIKE cause permits the use of any of the wildcard characters %, _ or ^. If the ^ character is placed in front of either of the other two wildcard characters, the characters following are treated as normal literals.
Example: If the column TEST contains '10% of the data' the expression TEST SLIKE '10^% of the data' returns TRUE, the expression TEST SLIKE '10%data' returns TRUE, and the expression TEST SLIKE '10data' returns FALSE.