Actuate SQL supports the following string functions and operators:
Case conversion functions: UPPER, LOWER
Concatenation operator: ||
Length function: CHAR_LENGTH
LIKE operator
Substring functions: LEFT, RIGHT, SUBSTRING
Trimming functions: LTRIM, RTRIM, TRIM
Search function: POSITION
Case conversion functions: UPPER, LOWER
These functions return a string formed by converting the characters in the argument to uppercase or lowercase respectively, provided the character is alphabetic:
FROM "../Data Sources/MyDatabase/CUSTOMERS.SMA" CUSTOMERS
returns:
101,Signal Engineering,signal engineering
109,InfoEngineering,infoengineering
111,Advanced Design Inc.,advanced design inc.
…
Concatenation operator: ||
This operator concatenates two string values, returning a new string that contains the characters from the left operand followed by the characters from the right operand.
Length function: CHAR_LENGTH
This function computes the length of a string, returning an integer count of its characters. Trailing spaces are significant:
Integer CHAR_LENGTH( value Varchar )
Example
The following code:
SELECT CUSTOMERS.CUSTID, CUSTOMERS.CONTACT_FIRST
FROM "../Data Sources/MyDatabase/CUSTOMERS.SMA" CUSTOMERS
WHERE CHAR_LENGTH(CUSTOMERS.CONTACT_FIRST) > 5
returns:
102,Leslie
109,Michael
116,William
…
LIKE operator
The LIKE operator is used in an expression such as:
column LIKE 'Mar%'
In this example, values of column, such as Mary or Martin, satisfy the test because both start with Mar.
A LIKE operator pattern must be a literal string, for example, 'abc%', a parameter, or an expression. The LIKE operator does not support column references, subqueries, or aggregate expressions. Other examples include:
column LIKE :paramState
column LIKE CURRENT_USER( )
The following rules apply:
Literal pattern characters must match exactly. LIKE is case-sensitive.
An underscore character (_) matches any single character.
A percent character (%) matches zero or more characters.
Escape a literal underscore, percent, or backslash character with a backslash character (\). Alternatively, use the following syntax:
test_string LIKE pattern_string ESCAPE escape_character
The escape character must obey the same rules as the LIKE operator pattern.
Substring functions: LEFT, RIGHT, SUBSTRING
These functions transform a string by retrieving a subset of its characters.
LEFT and RIGHT return the leftmost or rightmost n characters, respectively. Each takes the string as the first argument and the number of characters to retrieve as the second argument:
Varchar LEFT( value Varchar, offset Integer )
Varchar RIGHT( value Varchar, offset Integer )
Specifying an offset that is less than zero results in an error. If the offset is greater than the length of the string, these functions return the entire string.
SUBSTRING takes three arguments: the input string, the start position (one-based offset from the left side), and the number of characters to retrieve. It returns the substring located at this position:
FROM "../Data Sources/MyDatabase/CUSTOMERS.SMA" CUSTOMERS
returns:
101,Signal Engineering,ignal
102,Technical Specialists Co.,echni
104,SigniSpecialists Corp.,igniS
…
Trimming functions: LTRIM, RTRIM, TRIM
These functions strip space characters from a string. LTRIM strips only from the left side, RTRIM only from the right side, and TRIM from both sides. In all cases the result value is a string identical to the argument except for the possible removal of space characters from either side. Other white space characters, including tabs and newlines, are not removed by these functions:
Varchar LTRIM( value Varchar )
Varchar RTRIM( value Varchar )
Varchar TRIM( value Varchar )
Examples
The following code:
SELECT LTRIM(' Title '),'Author'
returns:
Title ,Author
The following code:
SELECT RTRIM(' Title '),'Author'
returns:
Title,Author
The following code:
SELECT TRIM(' Title '),'Author'
returns:
Title,Author
Search function: POSITION
The POSITION function takes two arguments: a substring and a search string. The POSITION function returns the position of the substring in the search string as an integer or as 0 if the substring is not found. If the substring is the empty string, the POSITION function returns 1. The POSITION function is case‑sensitive:
Case conversion functions: UPPER, LOWER