Standard SQL Functions

TextServer supports all of the specified SQL2 functions, as defined in the SQL2 standard. It also supports concatenate, arithmetic operations and parameters as defined in the standard. The functions supported are

• current_user
• session_user
• system_user
• sysdate
• systime
• sysstamp
• extract( YEAR|MONTH|DAY|HOUR|MINUTE|SECOND|TIMEZONE_HOUR|TIMEZONE_MINUTE FROM value)
• trim([[LEADING|TRAILING|BOTH] [string] FROM] string)
• upper(string)
• lower(string)
• substring(string FROM integer [FOR integer])
• length(string)
• bit_length(string)
• octet_length(string)
• position(string IN string)
• zone
• interval
• nullif(value, value)
• coalesce(value *[,value]*)
• case value *WHEN value THEN value* [ELSE value] END
• case *WHEN boolean THEN * [ELSE value] END
• count(*)
• count([DISTINCT|ALL] value)
• sum([DISTINCT|ALL] value)
• max([DISTINCT|ALL] value)
• min([DISTINCT|ALL] value)
• avg([DISTINCT|ALL] value)
• cast(value AS datatype)

Standard SQL predicates

• predicate IS [NOT] TRUE|FALSE|UNKNOWN
• row_value [NOT] BETWEEN row_value AND row_value
• row_value [NOT] IN (row_value *[,row_value]*)
• row_value [NOT] IN (subquery)
• string> [NOT] LIKE pattern [ESCAPE string])
• row_value IS [NOT] NULL
• EXISTS (subquery)
• UNIQUE (subquery)
• MATCH [UNIQUE] [PARTIAL|FULL] (subquery)
• row_value OVERLAPS row_value
• row_value =|<>|<|>|<=|>= [ANY|SOME|ALL] row_value

Additional non-standard SQL functions

power(double₁, double₂)

Raise the first double to the power of the second, returning the result.

aggregate_strings([DISTINCT|ALL] string [, separator] [ORDER BY value[ASC|DESC] [,..]])

This aggregate function concatenates the strings within each grouping optionally ordering them according to the specified order. If present each string except the first (in output sort order) will be preceded by the specified separator.

bookmark()

This function behaves like a column reference to a virtual field in each record of a table. This function returns successive integer values as successive rows are read, with the first row read having a bookmark of 1. This function may be qualified (in the same manner as a column reference) when it is necessary to explicitly establish the table it is to be applied against.

rowid()

This function behaves like a column reference to a virtual field in each record of an updatable table. It returns the internal rowid associated with records in such tables. This function may be qualified (in the same manner as a column reference) when it is necessary to explicitly establish the table it is to be applied against.