SuperMap iDesktopX
    简体中文11.0.0 GitHub
     /
     /
     /
     /

    Description on Building SQL Query Functions

    Introduction

    By constructing SQL query expressions, you can find out specific data from existed data. An expression consists of some operators and common functions. Common function includes aggregation functions, mathematical functions, character functions and data functions. For datasources in different engines, applicable functions are different. Following contents detail supportive situations.

    Operators

    The operators include mathematical, comparison, logical and some other operators.

    Mathematical Function Description Supportive Engines Example
    UDB/UDBX OraclePlus SQLPlus PostgreSQL DB2
    Abs Returns the absolute value of the expression. Applicable to fields (or expressions) in Short, Long, Single, or Double type. v v v v v Abs(World.SmID)
    Acos Returns the arccosine of the expression as an angle in radians ranging from -π to π. Applicable to fields or expressions in Single or Double type. The value of the expression ranges from -1 to 1. Not applicable to UDB datasources. v v v v v Acos(data)
    Asin Returns the arcsine of the expression as an angle in radians ranging from -π to π. Applicable to fields or expressions in Single or Double type. The value of the expression ranges from -1 to 1. Not applicable to UDB datasources. v v v v v Asin(data)
    Atan Returns the arctangent of the expression as an angle in radians. Function name is Atn for a SDB datasource. v v v v v Atan(data)
    Ceiling Returns the smallest integer greater than or equal to the expression. Applicable to fields (or expressions) in Short, Long, Single, or Double type. For Oracle datasources, the function is CEIL. Not applicable to UDB datasources. v v(CEIL) v v v Ceiling(data)
    Cos Returns the cosine of the expression as an angle in radians. v v v v v Cos(World.COLOR_MAP)
    cot Returns the cotangent of the expression as an angle in radians. v v v v v Cot(angle)
    Degrees Converts from radians to degrees. v v v v Degrees(angle)
    Exp Returns a Double value containing e (the base of natural logarithms) raised to the specified power. v v v v v Exp(data)
    Floor Rounds the specified number down and returns the largest number that is less than or equal to the specified number. v v v v v Floor(23.45), Floor(-123.45)
    Log Returns the natural logarithm of the specified expression. Not applicable to UDB datasources. For Oracle datasources, the function is Log(n, m), representing the logarithm of m with base n. v v v v v Log(World.SmID), Log(n,m)
    PI The Pi function returns the mathematical constant called pi. v v v PI()*World.SMID as Test
    Power Returns the value of parameter 1 to the power of parameter 2. Not applicable to SDB datasources. Applicable to Oracle datasources. v v v Power(expression,2) as Area
    Radians Converts degrees to radians. Applicable to SQL datasources. v v v v v Radians(angle) as NewAngle
    Rand Returns a float value less than 1, but greater than or equal to zero. v v Rand()
    Round Returns a number rounded to a specified number of decimal places. Parameter 1 and 2 respectively specify the number to round and the decimal places. The number nearest the specified value will be returned if you didn't specify the decimal places. v v v v v Round(World.SmArea,2), Round(World.SmArea)
    Sign Returns -1, 0, or +1 when the expression value is negative, zero, or positive respectively. v v v v v Sign(data)
    Sin Returns the sine of the expression value as an angle in radians. v v v v v Sin(World.COLOR_MAP)
    Square Returns the square of the expression value. v v v Square(Production)
    Sqrt Returns the square root of the expression value. For SDB datasources, the function is Sqrt. v v v v v Sqrt(Production)
    Tan Returns the tangent of the expression value. v v v v v Tan(World.COLOR_MAP)

    String Functions

    String functions are used to perform many types of manipulation on string data.

    String Function Description Supportive Engines Example
    UDB/UDBX OraclePlus SQLPlus PostgreSQL DB2
    Ascii Returns the ASCII code value of the leftmost character of a character expression. For a SDB datasource, function name is asc. Not applicable to UDB datasources.
    Syntax: ASCII(character_expression)character_expression is an expression of the type text.    		 v v v Ascii(String)
    Char Converts an int ASCII to a character. For SDB datasource, function name is Chr.
    Syntax: Char (integer_expression) integer_expression is an integer expression with value ranging from 0 to 255. NULL will be returned if the integer expression is not in this range.    		 v(Chr) v v(Chr) v(Chr) Char(data)
    InStr Returns the location of a substring in a string.
    Syntax: InSt([start_location],expr1,expr2) expr1 is the string to search. expr2 is the substring to search for in expr1. start_location is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1.     		v InStr(1,World.CAPITAL,"jing"), InStr(World.CAPITAL,"jing")
    Length Returns the length of the specified string. If the specified string1 is NULL, then the function returns NULL.
    Syntax: Length(string_expression) string_expression is the string to return the length for. For SDB/SQL datasources, the function is Len(string_expression).    		 v v v(Len) v v Lenth(World.CAPITAL)
    Lower Converts all letters in the specified string to lowercase. For SDB datasources, the function is LCase.
    Syntax: Lower (character_expression) character_expression is the string to convert to lowercase.    		 v v v v v Lower(World.CAPITAL) as capital
    Ltrim Returns a string containing a copy of a specified string with no leading spaces.
    Syntax: Ltrim (character_expression) character_exprssion is the string to trim.    		 v v v v v Ltrim(World.CAPITAL)
    Replace Replaces a sequence of characters in a string with another set of characters.
    		v v
    Reverse Returns string converted in the reverse order.
    Syntax: Reverse>( character_expression) character_expression is the string to reverse.    		 v v Reverse(World.CAPITAL) as NewName
    Rtrim Returns a string containing a copy of a specified string with no trailing spaces.
    Syntax: Rtrim(character_expression) character_expression is any valid string expression.    		 v v v v v Rtrim(World.CAPITAL)
    Soundex Returns a phonetic representation (the way it sounds) of a string.
    Syntax: Soundex (character_expression) character_expression the string whose phonetic value will be returned.    		 v v Soundex(word)
    Space Returns a string consisting of the specified number of spaces. If it is negative, NULL will be returned. v World.COUNTRY+Space(3)+World.CAPITAL as Countries_CAPITAL
    Substr Returns a part of text characters expression.
    Syntax: Substr(expression, start, length). expression is the expression of the string type that does not contain an aggregate function; start is an integer that specifies the starting position of the substring; length is an integer that specifies the length of the substring (the number of characters or bytes to return).     		v v(not for text) v v(or Substring) Substr ( string, position, 1 )
    Unicode According to the Unicode standard, return the integer value of first character from the entered expression.
    Syntax: Unicode('ncharacter_expression').    		 v Unicode(World.CAPITAL) as Unicode
    Upper Converts all letters in the specified string to uppercase. For SDB datasources, the function is UCase.
    Syntax: Upper(character_expression) character_expression is any valid string expression.    		 v v v v v Upper(World.CAPITAL)

    Date Functions

    Date functions are used to get information on date and time. These scalar functions perform an operation on a date and time input value and return a string, numeric, or date and time value. The date functions can be used anywhere in the expression.

    Date Function Description Supportive Engines Example
    UDB/UDBX OraclePlus SQLPlus PostgreSQL DB2
    Day Returns an integer representing the day datepart of the specified date. It equals to DatePartt( "d" ,date). Applicable to date fields or expressions of SQL/SDB datasources.
    Syntax: DatePart( datepart ,date)) date is an expression of type date.    		 v v Day(World.GETDATE) as Day
    GetDate Gets current data and time in standard SQL Syntax. v GetDate()
    GetUtcDate Returns the date value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running. v GetUtcDate()
    Month Returns an integer that represents the month part of a specified date. It equals to DatePart( "m" , date). Applicable to date fields or expressions of SQL/SDB datasources.
    Syntax: Month( date) date is a field or expression of date type.    		 v v Month(World.GETDATE) as Month
    Year Returns an integer that represents the year part of a specified date. It equals to DatePartdate. Applicable to date fields or expressions of SQL/SDB datasources.
    Syntax: Year(date) date is a date field or expression.    		 v v Year(World.GETDATE) as Year
    to_date For a string date, if you want to use it to construct a query condition, its format is fixed and you need to input it to the function to_data().
    Format: to_date(2018-5-11 14:28:00).    		 v Province_R.DataDate > to_date(2019-11-07 00:00:00)

    Tab: datepart values

    Value Description
    yyyy Year
    q Quarter
    m Month
    y Day of the year
    d Day
    w Weekday
    ww Week
    h Hour
    n Minute
    s Second
    ### Related Topics

    SQL Query Using Group By

    Constructing SQL Query Statements

    SQL Query Attribute Example

    SQL Relation Query Example