Common functions for building query statements include aggregate, math, string, and date, etc. In the SQL Query dialog, function help is provided to quickly view function explanations, select appropriate functions, and input them into expressions. The function help interface is shown below:
- Search box: Provides a function search box to search for specified strings in the function list and help descriptions, highlighting search results.
- Function list: Organized into groups such as recently used, operator, aggregate, math, string, and date for quick function location by type.
- Help description: Displays the explanation, 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 | Combines two Boolean expressions and returns TRUE only if 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 any value in the expression list. | Dataset.PROVINCE In ("GUANGDONG","BeiJing") |
| Between | Determines if an expression falls within a specified range, returning 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-type fields. | Dataset.Value < 0 Is TRUE |
| Is FALSE | Determines if an expression is FALSE, primarily for Boolean-type 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 for 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.
Except for the COUNT function, other aggregate functions ignore NULL values by default and cannot be applied to text data types.
In SuperMap iDesktopX, aggregate parameters are typically attribute tables and field names, such as Avg(Dataset.POP_2014), where "Dataset.POP_2014" denotes 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 the population sample based on a set of values in the specified field (or expression) of the query. 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 the population sample based on a set of values in the specified field (or expression) of the query. | × | √(Stddev_pop) | √ | √(Stddev_pop) | Stdevp(Dataset.POP_2014) |
| Var | Sample variance | Var(expression) returns the estimated variance of the population sample based on a set of values in the specified field (or expression) of the query. 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 the population sample based on a set of values in the specified field (or expression) of the query. | × | √(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 iDesktopX, 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 input. Applies to short integer, long integer, single-precision, double-precision, currency, and similar type fields (or expressions). |
√ | √ | √ | √ | Abs(Dataset.Temperature) |
| Acos |
Returns the angle in radians, ranging from -π to π, whose cosine is the given float expression. Applies to single-precision or double-precision type fields (or expressions). The expression value must range from -1 to 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. Applies to single-precision or double-precision type fields (or expressions). The expression value must range from -1 to 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. Applies to short integer, long integer, single-precision, double-precision, currency, and similar type fields (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 the 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 number of decimal places. The first parameter is the numeric field (or expression), and the second specifies decimal places. If omitted, it rounds to the 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 for character data processing.
| String | Meaning | Supported engines | Example | |||||
| UDB/UDBX | OraclePlus | SQLPlus | PostgreSQL | MySQL | ||||
| ASCII | Returns the ASCII code value of the leftmost character of the character expression. Format: ASCII(character_expression) where character_expression is an expression 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 position in expr1 to search for expr2, expr1 is a string expression containing characters to find, and expr2 is a string expression to search for. If start_location is omitted, searching starts 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, the function is 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, returning 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 string to find, and string_expr3 is the replacement string. |
√ | √ | × | × | × | Replace(Dataset.CAPITAL,"尔","而") | |
| Reverse |
Returns the reverse of the character expression. For Oracle data sources, Chinese characters may not process correctly or appear garbled. In Oracle and SQLPlus, "Char" is an expression of character data. In MySQL, "Char" can be an expression or a text field. |
× | √（Chinese characters may be garbled） | √ | × | √ | Reverse(Dataset.CAPITAL) as NewName | |
| Rtrim | Removes all trailing spaces from a character expression, returning 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 consisting 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). Expression is a non-aggregate string expression; start is an integer specifying the substring's start position; length is an integer specifying the substring length (number of characters or bytes to return). |
√ | √（not applicable to text） | √ | √（or Substring） | Substr ( string, position, 1 ) | ||
| Unicode | Returns the integer value of the first character of the input expression as defined by the Unicode standard. Format: Unicode('ncharacter_expression'). |
× | × | √ | × | × | Unicode(Dataset.CAPITAL) as Unicode | |
| Upper | Converts lowercase character data to uppercase and returns the character | |||||||