DataCaster SQL Functions

SQL Functions are functions that can be invoked in SQL statements.

SQL Functions are grouped into three categories.

  • Builtin Functions are SQL functions defined in the SQL standard and built into DataCaster.
  • Configured Functions are administrator controlled functions in functions.xml configuration file.
  • Schema Functions are specific to schemas and are added or dropped using SQL statements or the API.

Builtin Functions

DataCaster provides a few built-in functions, i.e. as defined by the SQL standard. This is only a small subset of the typically available set of functions.

The following are the list of Builtin Funtions:

  • Lower :Get the string converted to lower case.

    Syntax: LOWER "(" ColumnExpression ")"

    Eg: LOWER('MyName') -- myname

  • Upper :Get the string converted to upper case.

    Syntax: UPPER "(" ColumnExpression ")"

    Eg: UPPER('MyName') -- MYNAME

  • Trim : Get the string from which, the leading/trailing spaces(or specifed string) or both are trimmed depending on the function parameters.

    Syntax:
    TRIM "("
    [ [ LEADING | TRAILING | BOTH ] [     ColumnExpression ] FROM ]
    ColumnExpression ")"

    Eg:
    TRIM(LEADING FROM ' NAME ') -- Trims only the leading spaces from the string. Result : 'NAME '

    TRIM(TRAILING FROM ' NAME ') -- Trims only the trailing spaces from the string. Result : ' NAME'

    TRIM(' NAME ') -- Trims spaces on both the sides of the string. This is equivalent to TRIM(BOTH FROM ' NAME ') -- Result : 'NAME'

    TRIM(LEADING 'A' FROM 'AMERICA') -- Trims only the leading 'A' from the string. Result : 'MERICA'

    TRIM(TRAILING 'A' FROM 'AMERICA') -- Trims only the trailing 'A' from the string. Result : 'AMERIC'

    TRIM('A' FROM 'AMERICA') -- Trims 'A' on both the sides of the string. This is equivalent to TRIM(BOTH FROM 'A' FROM 'AMERICA') -- Result : 'MERIC'

  • SubString : Get the substring of a string from specified position upto specified length.

    Syntax:
    SUBSTRING "(" ColumnExpression FROM Position [ FOR Length ] ")"

    Eg:
    SUBSTRING('MYNAME' FROM 3) -- 'NAME'
    SUBSTRING('ATTRACTION' FROM 3 FOR 5) -- 'TRACT'

  • Concatenate : Get a string which is the result of concatenation of two or more strings.

    Syntax:
    CONCATENATE "(" ColumnExpression ( "," ColumnExpression )+ ")"

    Eg:
    CONCATENATE('SOUTH', 'INDIA') -- 'SOUTHINDIA'
    CONCATENATE('SOUTH', ' ', 'INDIA') -- 'SOUTH INDIA'
    CONCATENATE('SOUTH', '-', 'INDIA') -- 'SOUTH-INDIA'

  • Char_Length : Get the length of the argument in terms of bytes.

    Syntax: CHAR_LENGTH "(" ColumnExpression ")"

    Eg: CHAR_LENGTH('MyName') -- 6

  • Bit_Length : Get the length of the argument in terms of bits.

    Syntax: BIT_LENGTH "(" ColumnExpression ")"

    Eg: BIT_LENGTH('MyName') -- 48

  • Octet_Length : Get the length of the argument in terms of octets.

    Syntax: OCTET_LENGTH "(" ColumnExpression ")"

    Eg: OCTET_LENGTH('MyName') -- 6

  • Position : Get the first character position of a string in another string.

    Syntax: POSITION "(" ColumnExpression IN ColumnExpression ")"

    Eg: POSITION('Name' IN 'MyName') -- 3

  • Abs : Get the absolute value of an expression. The negative sign if any, is removed. Decimal values are rounded up.

    Syntax: ABS "(" ColumnExpression ")"

    Eg: ABS(-23) -- 23 ; ABS(23.04) -- 23; ABS(-12.39) -- 12

  • Extract : Extract the numeric value of date_time field from a temporal data types. The data_time fields supported for Extract function are : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAY_OF_YEAR, WEEK_OF_YEAR, QUARTER.

    Syntax: EXTRACT "(" DateTimeField FROM TemporalDataType ")"

    Eg:
    EXTRACT(YEAR FROM CURRENT_TIMESTMAP) -- 2005
    EXTRACT(MONTH FROM CURRENT_TIMESTMAP) -- 11
    EXTRACT(QUARTER FROM CURRENT_TIMESTMAP) -- 4

  • Overlay : Get a substring of a string, by replacing it with the specified string at the specified position.

    Syntax:
    OVERLAY "(" ColumnExpression
    PLACING     ColumnExpression
    FROM     Position [ FOR Length ] ")"

    Eg:
    OVERLAY('STATE' PLACING 'L' FROM 2) -- 'SLATE'
    OVERLAY('APPLIBASE' PLACING 'CATION' FROM 6) -- 'APPLICATION'
    OVERLAY('APPLICANT SOFTWARE' PLACING 'BASEMENT' FROM 6 FOR 4) -- 'APPLIBASE SOFTWARE' (Only 4 characters are replaced as specified by the FOR clause).

  • Last_Insert_Id :Get the last insert id for the current user.

    Syntax:
    LAST_INSERT_ID

Administrator Configured Functions

Many SQL functions in DataCaster are provided via the functions.xml configuration file, where new functions available to all users are configured. Administrators can edit this file and provide additional functions as needed. While pre-defined functions shipped with DataCaster may also be dropped if required, this should be used with caution to avoid breaking applications that rely on these functions.

The following are the list of configured functions shipped with DataCaster.

  • MATH_SIN : Mathematical sin Function.

    Syntax:
    MATH_SIN "(" ColumnExpression*")*
    Eg:
    math_sin( 90 ) -0.234

  • MATH_COS : Mathematical cos function

    Syntax:
    MATH_COS "(" ColumnExpression*")*
    Eg:
    math_cos(30) + math_cos(45)

  • COT : Mathematical cot function

    Syntax:
    COT "(" ColumnExpression*")*
    Eg:
    cot(30) + cot(45)

  • TAN : Mathematical tan function

    Syntax:
    TAN "(" ColumnExpression*")*
    Eg:
    tan(30) + tan(45)

  • MATH_TRUNCATE: mathematical truncate operaton.

    Syntax:
    MATH_TRUNCATE "(" Value*ColumnExpression,
    *PLACES* integer *")*
    Eg:
    math_truncate( "2.534567",3) -- Tuncates "2.534567" to the third digit and returns 2.535
    math_truncate( "2.534367",3) -- Tuncates "2.534367" to the third digit and returns 2.534
    math_truncate( "-2.534367",3) -- Tuncates "-2.534367" to the third digit and returns -2.535

  • ACOS : Mathematical acos function

    Syntax:
    ACOS"(" ColumnExpression*")*
    Eg:
    acos(30) + acos(45)

  • ASIN : Mathematical asin function

    Syntax:
    ASIN "(" ColumnExpression*")*
    Eg:
    asin(30) + asin(45)

  • ATAN : Mathematical atan function

    Syntax:
    ATAN "(" ColumnExpression*")*
    Eg:
    atan(30) + atan(45)

  • CEILING: Mathematical ceil function that returns the smallest (closest to negative infinity) double value that is not less than the argument and is equal to a mathematical integer.

    Syntax:
    CEILING"(" ColumnExpression *")*
    Eg:
    45*Ceiling(1.45) -- is equivalent to 45*2
    45*Ceiling(-1.45) -- is equivalent to 45*(-1)

  • FLOOR: Mathematical floor function that returns the largest (closest to positive infinity) double value that is not greater than the argument and is equal to a mathematical integer.

    Syntax:
    FLOOR"(" ColumnExpression *")*
    Eg:
    45*Floor(1.45) -- is equivalent to 45*1
    45*Floor(-1.45) -- is equivalent to 45*(-2)

  • ROUND: Rounds the first argument value to number od decimal places specified by the second argument.

    Syntax:
    ROUND"("VALUE ColumnExpression *,PLACES*
    ColumnExpression *")*
    Eg:
    round(1.456,2) -- returns 1.46
    round(-1.456,2) -- returns -1.46

  • MATH_MIN : Mathematical min function that returns the smaller of two int values

    Syntax:
    MATH_MIN "(" ColumnExpression *,*
    ColumnExpression*")*
    Eg:
    math_min(3,-1) -- returns -1.

  • EXP : Mathematical exp function that returns Euler's number e raised to the power of a double value

    Syntax:
    EXP "(" ColumnExpression*")*
    Eg:
    exp(1.45) -- returns 4.263114515168817

  • LOG: Mathematical log function, returns the natural logarithm (base e) of a double value.

    Syntax:
    LOG"(" ColumnExpression *")*
    Eg:
    45*log(1.45) -- is equivalent to 45*0.371563556432483

  • LOG10: Mathematical log10 function, returns the logarithm (base 10) of a double value.

    Syntax:
    LOG10"(" ColumnExpression *")*
    Eg:
    45*log10(1.45) -- is equivalent to 45*0.16316137497701835

  • RADIANS: Converts an angle measured in degrees to an approximately equivalent angle measured in radians.

    Syntax:
    RADIANS"(" ColumnExpression *")*
    Eg:
    radians(45) -- is equivalent to 0.7853981633974483

  • DEGREES: Converts an angle measured in radians to an approximately equivalent angle measured in degrees.

    Syntax:
    DEGREES"(" ColumnExpression *")*
    Eg:
    degrees(0.7853981633974483) -- is equivalent to 45

  • POWER: Returns the value of the first argument raised to the power of the second argument.

    Syntax:
    DEGREES"(" ColumnExpression *,*
    ColumnExpression *")*
    Eg:
    power(13,2) -- returns 169

  • SQRT: Returns the correctly rounded positive square root of a double value.

    Syntax:
    SQRT"(" ColumnExpression *")*
    Eg:
    sqrt(169) -- returns 13

  • MOD: Returns the modulos of argument one to argument2

    Syntax:
    MOD"(" ColumnExpression *,*
    ColumnExpression *")*
    Eg:
    MOD(3,2) -- returns 1
    MOD(3,5) -- returns 3

  • SIGN: Returns 1,-1 or 0 if the argument value is positive , negative or zero respectively.

    Syntax:
    SQIGN"(" ColumnExpression *")*
    Eg:
    sqrt(23) -- Returns 1
    sqrt(-23) -- Returns -1
    sqrt(0) -- Returns 0

  • RAND: Returns a random double value that depends on the provided seed value.

    Syntax:
    RAND"(" ColumnExpression *")*
    Eg:
    rand(12)

  • PI: Returns the double value of PI.

    Syntax:
    *PI*
    Eg:
    PI - returns 3.14159265358979323846

Schema Functions

Schema functions are defined by applications and users as needed in specific databases and schemas. These functions can be added or dropped at any time.