Common functions for building query statements include aggregate, math, string, and date functions. In the SQL Query dialog, function help is provided to quickly view function descriptions, select the appropriate function, and input it into the expression. The function help interface is as follows:
- Search box: Provides a function search box to search for specified strings in the function list and help descriptions, highlighting the search results.
- Function list: Grouped by recently used, operator, aggregate, math, string, and date, allowing quick location of functions based on function type.
- Help description: Displays the description, syntax, parameter information, and usage examples for the selected function in the function list.
In all tables below, the content in parentheses after the supported engines column indicates the writing format of the function under the corresponding database engine. For example, "√(Stddev)" means that in OraclePlus and PostgreSQL databases, it should be written as "Stddev".
Operator
Operators are used to construct SQL Query conditions, including common mathematical operators, logical operators, and some special operators.
| Operator | Meaning | Example |
|---|---|---|
| Arithmetic Operators | ||
| ＋ | Addition | Dataset.RENT + Dataset.UTILITIES <= 800 |
| - | Subtraction | Dataset.POP_2019 - Dataset.POP_2010 |
| * | Multiplication | Dataset.DENSITY * Dataset.AREA > 5000000 |
| / | Division | Dataset.VALUE / Dataset.POP = 50000 |
| \ | Quotient | Dataset.VALUE \ Dataset.POP > 100 |
| % | Modulo | Dataset.VALUE1 % Dataset.VALUE2 |
| Mod | Modulo | Dataset.VALUE1 Mod Dataset.VALUE2 |
| Comparison Operators | ||
| = | Equal to | Dataset.CODE = 100 |
| > | Greater than | Dataset.POP_2002 > 5000000 |
| < | Less than | Dataset.INDUST_GROWTH < 0 |
| >= | No less than | Dataset.RENT + Dataset.UTILITIES >= 800 |
| <= | No more than | Dataset.RENT + Dataset.UTILITIES <= 800 |
| <> | Not equal to | Dataset.VALUE <> 100 |
| ! | Not, used with ">" or "<" to form !< (not less than) or !> (not greater than) | Dataset.VALUE !> 100 |
| Logical Operators | ||
| AND | Connects two Boolean expressions and returns TRUE when both expressions are TRUE. | Dataset.CODE = 100 AND Dataset.VALUE > 20000 |
| NOT | Negates the value of any other Boolean operator. | NOT IsBACHELOR |
| OR | Combines two conditions and returns TRUE if at least one Boolean expression is TRUE. | Dataset.SALES > 20000 OR Dataset.ORDERS > 20000 |
| IN | Returns TRUE if the operand equals one in the expression list. | Dataset.PROVINCE In ("GUANGDONG","BeiJing") |
| Between | Determines if an expression falls within a specified range and returns TRUE. Typically used with AND. | Dataset.SALES Between 4095 AND 20000 |
| Like | Determines if search results exactly match the specified pattern. | Dataset.COUNTRY Like "CANADA" |
| Other | ||
| Is NULL | Determines if an expression is NULL, primarily for text-type fields. | Dataset.CONTINENT Is NULL |
| Is TRUE | Determines if an expression is True, primarily for Boolean fields. | Dataset.Value < 0 Is TRUE |
| Is FALSE | Determines if an expression is False, primarily for Boolean fields. | Dataset.Value > 0 Is FALSE |
| # | Match any single numeric character. | Dataset.SmID like '1#3' |
| ´ | Used for input of character-type fields, date-type fields, etc. | Dataset.COUNTRY like '*国' |
| ˆ | Calculate the value of a raised to the power of b. It does not support UDB/UDBX, Oracle, SQLPlus, or MySQL engines but is applicable to the PostGIS engine. | Dataset.SmID > 2^3 |
| · | Used when manually inputting select fields. | Dataset.COUNTRY |
| * | Match any number of characters. Can be used anywhere in the string. | Dataset.COUNTRY like '*国' |
Aggregate
Aggregate functions are used in database query languages to perform specific calculations on a set of values, summarizing multiple rows or columns into a single result value.
Except for the COUNT function, other aggregates ignore null values by default and cannot be applied to text data types.
In SuperMap ImageX Pro , aggregate parameters are typically attribute tables and field names, such as Avg(Dataset.POP_2014), where "Dataset.POP_2014" indicates the attribute table name "Dataset" and field "POP_2014".
To use aggregates in the SQL Query dialog, directly enter the expression in the result reserved fields. For example, to query the maximum value of the "SmArea" field, enter "Max(SmArea)" in the result reserved fields.
| Aggregate | Chinese Name | Meaning | Supported Engines | Example | |||
| UDB/UDBX | OraclePlus | SQLPlus | PostgreSQL | ||||
| Avg | Average (Mean) | AVG(expression) returns the arithmetic mean of a set of values in the specified field (or expression) of the query. Null values are ignored. | √ | √ | √ | √ | Avg(Dataset.POP_2014) |
| Count | Count | COUNT(expression) returns the number of records in each group. | √ | √ | √ | √ | Count(*) |
| Max | Maximum | Max(expression) returns the maximum value in the specified field (or expression) of the query. | √ | √ | √ | √ | Max(Dataset.POP_2014) |
| Min | Minimum | Min(expression) returns the minimum value in the specified field (or expression) of the query. | √ | √ | √ | √ | Min(Dataset.POP_2014) |
| Sum | Sum (Total) | Sum(expression) returns the total of a set of values in the specified field (or expression) of the query. | √ | √ | √ | √ | Sum(Dataset.POP_2014) |
| Stdev | Sample Standard Deviation | Stdev(expression) returns the estimated standard deviation of a sample population from a set of values in the specified field (or expression). For Oracle data sources, the function name is STDDEV. | √ | √(Stddev) | √ | √(Stddev) | Stdev(Dataset.POP_2014) |
| Stdevp | Population Standard Deviation | Stdevp(expression) returns the estimated standard deviation of a population sample from a set of values in the specified field (or expression). | × | √(Stddev_pop) | √ | √(Stddev_pop) | Stdevp(Dataset.POP_2014) |
| Var | Sample Variance | Var(expression) returns the estimated variance of a sample population from a set of values in the specified field (or expression). For Oracle data sources, the function name is VARIANCE. | √ | √(Variance) | √ | √(Variance) | Var(Dataset.POP_2014) |
| Varp | Population Variance | Varp(expression) returns the estimated variance of a population sample from a set of values in the specified field (or expression). | × | √(Var_pop) | √ | √(Var_pop) | Varp(Dataset.POP_2014) |
Math
Math functions take one or more parameters and return a result after specific mathematical operations.
In SuperMap ImageX Pro , expressions typically use field names from attribute tables, such as Cos(Slope), where "Slope" is the field name.
| Math | Meaning | Supported Engines | Example | |||
| UDB/UDBX | OraclePlus | SQLPlus | PostgreSQL | |||
| Abs |
Returns the absolute value of the specified number, with the same type as the specified data type. Applicable to short integer, long integer, single precision, double precision, currency, and similar field types (or expressions). |
√ | √ | √ | √ | Abs(Dataset.Temperature) |
| Acos |
Returns the angle in radians, ranging from -π to π, whose cosine is the given float expression. Applicable to single or double precision field types (or expressions), with expression values between -1 and 1; otherwise, the query result is null. |
√ | √ | √ | √ | Acos(data) |
| Asin |
Returns the angle in radians, ranging from -π to π, whose sine is the given float expression. Applicable to single or double precision field types (or expressions), with expression values between -1 and 1; otherwise, the query result is null. |
√ | √ | √ | √ | Asin(data) |
| Atan | Returns the angle in radians whose tangent is the given float expression. | √ | √ | √ | √ | Atan(data) |
| Ceiling |
Returns the smallest integer greater than or equal to the given numeric expression. Applicable to short integer, long integer, single precision, double precision, currency, and similar field types (or expressions). For Oracle data sources, the function name is CEIL. |
√ | √(CEIL) | √ | √ | Ceiling(data) |
| Cos | Returns the trigonometric cosine of the given angle (in radians) in the expression as a double. | √ | √ | √ | √ | Cos(Dataset.Radian) |
| cot | Returns the trigonometric cotangent of the specified angle (in radians) in the given float expression. | √ | √ | √ | √ | Cot(angle) |
| Degrees | When given an angle in radians, returns the corresponding angle in degrees. | √ | × | √ | √ | Degrees(angle) |
| Exp | Returns the base e (approximately 2.71828182845905) exponent of the given float expression. | √ | √ | √ | √ | Exp(data) |
| Floor | Returns the largest integer less than or equal to the given mathematical expression. | √ | √ | √ | √ | Floor(23.45), Floor(-123.45) |
| Log |
Returns the natural logarithm of the given mathematical expression. Not suitable for UDB data sources. For Oracle data sources, the function format is Log(n,m), representing the logarithm of m to base n. |
√ | √ | √ | √ | Log(Dataset.SmID), Log(n,m) |
| PI | Returns the constant value of PI; function input is empty. | √ | √ | √ | PI()*Dataset.SMID as Test | |
| Power | Returns the value of the given expression raised to the specified power, with the first parameter as the numeric field (or expression) and the second as the exponent. | √ | √ | √ | Power(expression,2) as Area | |
| Radians | Returns the radian value for a degree value entered in a numeric expression, with the radian value rounded down. | √ | √ | √ | √ | Radians(angle) as NewAngle |
| Rand | Returns a random float value between 0 and 1; function input is empty. | √ | Rand() | |||
| Round | Returns the specified numeric field (or expression) rounded to the specified decimal places. The first parameter is the numeric field (or expression), and the second is the decimal count. If omitted, defaults to nearest integer. | √ | √ | √ | √ | Round(Dataset.SmArea,2), Round(Dataset.SmArea) |
| Sign | Returns the sign of the given expression: positive (+1), zero (0), or negative (-1). | √ | √ | √ | √ | Sign(data) |
| Sin | Returns the trigonometric sine of the given angle (in radians) as a double. | √ | √ | √ | √ | Sin(Dataset.COLOR_MAP) |
| Square | Returns the square of the given expression. | × | × | √ | √ | Square(Production) |
| Sqrt | Returns the square root of the given expression. | √ | √ | √ | √ | Sqrt(Production) |
| Tan | Returns the tangent of the input expression. | √ | √ | √ | √ | Tan(Dataset.COLOR_MAP) |
String
String functions perform operations on character and binary strings, returning values needed to process character data.
| String | Meaning | Supported Engines | Example | |||||
| UDB/UDBX | OraclePlus | SQLPlus | PostgreSQL | MySQL | ||||
| ASCII | Returns the ASCII code value of the leftmost character in the character expression. Format: ASCII(character_expression), where character_expression is an expression composed of text fields. |
√ | √ | √ | √ | Ascii(String) | ||
| Char | Converts the value of an int expression from ASCII to a character string. Format: Char(integer_expression), where integer_expression is an integer between 0 and 255. Returns NULL if out of range. |
√ | √(Chr) | √ | √(Chr) | √ | Char(data) | |
| InStr | Returns the starting position of a specified expression within a string. Format: InStr([start_location],expr1,expr2), where start_location is the starting character position in expr1 to search for expr2, expr1 is the string expression to search, and expr2 is the sequence to find. If start_location is omitted, search begins from the first character. |
× | √ | × | × | × | InStr(1,Dataset.CAPITAL,"京"), InStr(Dataset.CAPITAL,"京") | |
| Length | Returns the length (number of characters) of the given string expression, including spaces. Returns null if the field is empty. Format: Length(string_expression), where string_expression is the string to evaluate. For SQL data sources, use Len(string_expression). |
√ | √ | √(Len) | √ | √ | Lenth(Dataset.CAPITAL) | |
| Lower | Converts uppercase character data to lowercase and returns the character expression. Format: Lower(character_expression), where character_expression is a char expression. |
√ | √ | √ | √ | √ | Lower(Dataset.CAPITAL) as capital | |
| Ltrim | Removes leading spaces from the specified string expression. Returns a string without leading spaces. Format: Ltrim(character_expression), where character_expression is a char expression. |
√ | √ | √ | √ | √ | Ltrim(Dataset.CAPITAL) | |
| Replace | Replaces all occurrences of the second string expression with the third expression in the first string expression. Format: Replace('string_expr1','string_expr2','string_expr3'), where string_expr1 is the string to search, string_expr2 is the substring to find, and string_expr3 is the replacement string. |
√ | √ | × | × | × | Replace(Dataset.CAPITAL,"尔","而") | |
| Reverse |
Returns the reverse of the character expression. Chinese characters may not process correctly or become garbled when querying Oracle data sources. For Oracle and SQLPlus, "Char" is an expression of character data. For MySQL, "Char" can be an expression or text field. |
× | √（Chinese characters may be garbled） | √ | × | √ | Reverse(Dataset.CAPITAL) as NewName | |
| Rtrim | Removes trailing spaces from a character expression and returns a string without trailing spaces. Format: Rtrim(character_expression), where character_expression is an expression of character data. |
√ | √ | √ | √ | √ | Rtrim(Dataset.CAPITAL) | |
| Soundex | Returns a four-character code (SOUNDEX) to evaluate the similarity of two strings. Format: Soundex(character_expression), where character_expression is an alphanumeric expression of character data. |
× | √ | √ | × | × | Soundex(word) | |
| Space | Generates a string of the specified number of spaces. Returns an empty string if the number is negative. | × | × | √ | × | × | Dataset.COUNTRY+Space(3)+Dataset.CAPITAL as World_CAPITAL | |
| Substr | Returns part of a text character expression. Format: Substr(expression, start, length), where expression is a non-aggregate string expression, start is an integer for the substring start position, and length is an integer for the substring length (number of characters or bytes to return). |
√ | √（not applicable for text） | √ | ||||