PC SOFT

ONLINE HELP
 WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English EN
This content has been translated automatically. Click here to view the French version.
  • Overview
  • Mathematical SQL functions
  • ABS
  • ACOS
  • ASIN
  • ATAN
  • ATAN2
  • BIN
  • CAST
  • CBRT
  • CEILING
  • CEIL
  • COS
  • DEGREES
  • DIV
  • EXP
  • FLOOR
  • HEX
  • LN
  • LOG
  • LOG10
  • MOD
  • OCT
  • PI
  • POWER
  • RADIANS
  • RANDOM
  • ROUND
  • SIGN
  • SIN
  • SQRT
  • STDDEV
  • STDDEV_POP
  • STDDEV_SAMP
  • TAN
  • TRUNC
  • UNHEX
  • VARIANCE
  • VAR_POP
  • VAR_SAMP
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Overview
The mathematical SQL functions that can be used on the queries written in SQL code are as follows:

See a specific documentation about the SQL language for more details.
To discover all the SQL commands (functions, clauses, operators, etc.) that can be used in an SQL query managed by HFSQL, see Commands that can be used in an SQL query managed by HFSQL.
Remarks:
  • These statements can be used:
    • in the SQL code of queries created in the query editor. Then, these queries will be run by HExecuteQuery.
    • in the SQL code of queries run by HExecuteSQLQuery.
  • Unless stated otherwise, these functions can be used with all types of data sources (Oracle, Access, SQL Server, ...).
Remark: From version 19, HFSQL is the new name of HyperFileSQL.
Mathematical SQL functions

ABS

ABS is used to find out the absolute value of a number.
Use format:
ABS(Number)

ACOS

ACOS is used to find out the cosine angle of a number.
Use format:
ACOS(Number)

ASIN

ASIN is used to find out the sine angle of a number.
Use format:
ASIN(Number)

ATAN

ATAN is used to find out the tangent angle of a number.
Use format:
ATAN(Number)
Versions 16 and later

ATAN2

ATAN2 is used to find out the arctangent of the X and Y variables. This function is equivalent to the calculation of the arctangent of Y/X except that the signs of the two arguments are used to define the quadrant of the result.
Use format:
ATAN2(Y,X)
New in version 16

ATAN2

ATAN2 is used to find out the arctangent of the X and Y variables. This function is equivalent to the calculation of the arctangent of Y/X except that the signs of the two arguments are used to define the quadrant of the result.
Use format:
ATAN2(Y,X)

ATAN2

ATAN2 is used to find out the arctangent of the X and Y variables. This function is equivalent to the calculation of the arctangent of Y/X except that the signs of the two arguments are used to define the quadrant of the result.
Use format:
ATAN2(Y,X)

BIN

BIN returns the character string representing an "integer" in binary format. This integer must be found between 0 and 2 to the power of 63-1.
Native Connectors (Native Accesses) This function cannot be used on a PostgreSQL data source.
Use format:
BIN(Integer)
Versions 17 and later

CAST

CAST is used to convert a number into another one.
Use format:
CAST(Number AS Type)
Use example:
  • Converts the GrandTotal(currency) into an integer (no decimal):
    CAST(orders.grandtotal AS INTEGER)
  • Convert the totalIOT (currency) into 6-digit numeric among which 2 digits for the decimal part.
    CAST(orders.grandtotal AS decimal (6,2))
New in version 17

CAST

CAST is used to convert a number into another one.
Use format:
CAST(Number AS Type)
Use example:
  • Converts the GrandTotal(currency) into an integer (no decimal):
    CAST(orders.grandtotal AS INTEGER)
  • Convert the totalIOT (currency) into 6-digit numeric among which 2 digits for the decimal part.
    CAST(orders.grandtotal AS decimal (6,2))

CAST

CAST is used to convert a number into another one.
Use format:
CAST(Number AS Type)
Use example:
  • Converts the GrandTotal(currency) into an integer (no decimal):
    CAST(orders.grandtotal AS INTEGER)
  • Convert the totalIOT (currency) into 6-digit numeric among which 2 digits for the decimal part.
    CAST(orders.grandtotal AS decimal (6,2))
Versions 15 and later

CBRT

CBRT returns the cube root of a number.
Use format:
CBRT(Number)
New in version 15

CBRT

CBRT returns the cube root of a number.
Use format:
CBRT(Number)

CBRT

CBRT returns the cube root of a number.
Use format:
CBRT(Number)

CEILING

Versions 15 and later

CEIL

New in version 15

CEIL

CEIL

CEILING and CEIL return the rounded-up value of a number.
Use format:
CEILING(Number)
CEIL(Number)

COS

COS is used to find out the cosine of a number.
Use format:
COS(Number)
Versions 15 and later

DEGREES

DEGREES is used to convert an angle expressed in radians into degrees.
Use format:
DEGREES(Number)
New in version 15

DEGREES

DEGREES is used to convert an angle expressed in radians into degrees.
Use format:
DEGREES(Number)

DEGREES

DEGREES is used to convert an angle expressed in radians into degrees.
Use format:
DEGREES(Number)
Versions 15 and later

DIV

DIV performs an entire division.
Use format:
DIV(Dividend, Divisor)
New in version 15

DIV

DIV performs an entire division.
Use format:
DIV(Dividend, Divisor)

DIV

DIV performs an entire division.
Use format:
DIV(Dividend, Divisor)

EXP

EXP is used to find out the exponential value of a number.
Use format:
EXP(Number)

FLOOR

FLOOR is used to find out the rounded-down value of a number.
Use format:
FLOOR(Number)

HEX

HEX returns the hexadecimal value of an integer (expressed in base 10). This integer must be found between 0 and 2 to the power of 63-1.
Remark: Used with the HFSQL engine, HEX returns a string starting with the high byte.
Native Connectors (Native Accesses) Special cases:
  • Native DB2 Access: HEX used on the numeric values returns a character string starting with the low byte.
  • Native PostgreSQL Access: This function cannot be used on a PostgreSQL data source.
Use format:
HEX(Integer)
Example on HFSQL: HEX(258) = 0102
Example on DB2: HEX(258) = 0201

LN

LN is used to find out the Napierian logarithm of a number.
Use format:
LN(Number)

LOG

LOG is used to find out the Napierian logarithm of a number.
Use format:
LOG(Number)
Other possible syntax: Base-x logarithm of a number:
LOG(Base, Number)

LOG10

LOG10 is used to find out the decimal logarithm of a number.
Native Connectors (Native Accesses) This function cannot be used on a PostgreSQL data source.
Use format:
LOG10(Number)

MOD

MOD is used to find out the remainder of a division between two numbers (modulo).
Use format:
MOD(Dividend, Divisor)

OCT

OCT returns an integer in octal (expressed in base 10). This integer must be found between 0 and 2 to the power of 63-1.
Native Connectors (Native Accesses) This function cannot be used on a PostgreSQL data source.
Use format:
OCT(Integer)

PI

PI is used to find out the value of PI.
Use format:
PI()

POWER

POWER is used to find out the value of a number raised to a power.
Use format:
POWER(Number, Power)
Versions 15 and later

RADIANS

RADIANS converts an angle expressed in degrees into radians.
Use format:
RADIANS(Number)
New in version 15

RADIANS

RADIANS converts an angle expressed in degrees into radians.
Use format:
RADIANS(Number)

RADIANS

RADIANS converts an angle expressed in degrees into radians.
Use format:
RADIANS(Number)
Versions 15 and later

RANDOM

RANDOM returns a number included between 0.0 and 1.0 (inclusive).
Versions 22 and later
RAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive).
New in version 22
RAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive).
RAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive).
Use format:
  • RANDOM:
    RANDOM()
  • Versions 22 and later
    RAND:
    RAND()
    New in version 22
    RAND:
    RAND()
    RAND:
    RAND()
New in version 15

RANDOM

RANDOM returns a number included between 0.0 and 1.0 (inclusive).
Versions 22 and later
RAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive).
New in version 22
RAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive).
RAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive).
Use format:
  • RANDOM:
    RANDOM()
  • Versions 22 and later
    RAND:
    RAND()
    New in version 22
    RAND:
    RAND()
    RAND:
    RAND()

RANDOM

RANDOM returns a number included between 0.0 and 1.0 (inclusive).
Versions 22 and later
RAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive).
New in version 22
RAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive).
RAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive).
Use format:
  • RANDOM:
    RANDOM()
  • Versions 22 and later
    RAND:
    RAND()
    New in version 22
    RAND:
    RAND()
    RAND:
    RAND()

ROUND

ROUND is used to round up a number according to the number of decimal places.
Use format:
ROUND(Number, Decimal places)
Example: The following SQL code is used to round up the price of products to 2 decimal places:
SELECT Designation,
ROUND(PriceBT, 2) AS Price
FROM PRODUCT

SIGN

SIGN returns 1, 0 or -1 depending on whether the given number is positive, null or negative.
Use format:
SIGN(Number)

SIN

SIN is used to find out the sine of a number.
Use format:
SIN(Number)
Remark: The Number parameter is expressed in radians.

SQRT

SQRT is used to find out the square root of a number.
Use format:
SQRT(Number)
Versions 17 and later

STDDEV

STDDEV is used to find out the standard deviation for a series of values. STDDEV is used when the series of values represents a sample of the data population. This function is equivalent to STDDEV_SAMP.
The standard deviation is the square root of the variance.
Use format:
STDDEV(Number)
Example: Mean and standard deviation of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
STDDEV_SAMP(orders.totaliot) AS standarddev FROM orders GROUP BY year
New in version 17

STDDEV

STDDEV is used to find out the standard deviation for a series of values. STDDEV is used when the series of values represents a sample of the data population. This function is equivalent to STDDEV_SAMP.
The standard deviation is the square root of the variance.
Use format:
STDDEV(Number)
Example: Mean and standard deviation of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
STDDEV_SAMP(orders.totaliot) AS standarddev FROM orders GROUP BY year

STDDEV

STDDEV is used to find out the standard deviation for a series of values. STDDEV is used when the series of values represents a sample of the data population. This function is equivalent to STDDEV_SAMP.
The standard deviation is the square root of the variance.
Use format:
STDDEV(Number)
Example: Mean and standard deviation of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
STDDEV_SAMP(orders.totaliot) AS standarddev FROM orders GROUP BY year
Versions 17 and later

STDDEV_POP

STDDEV_POP is used to find out the standard deviation for a series of values. STDDEV_POP is used when the series of values represents the entire data population.
The standard deviation is the square root of the variance.
Use format:
STDDEV_POP(Number)
Example: Mean and standard deviation of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
STDDEV_POP(orders.totaliot) AS standarddev FROM orders GROUP BY year
New in version 17

STDDEV_POP

STDDEV_POP is used to find out the standard deviation for a series of values. STDDEV_POP is used when the series of values represents the entire data population.
The standard deviation is the square root of the variance.
Use format:
STDDEV_POP(Number)
Example: Mean and standard deviation of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
STDDEV_POP(orders.totaliot) AS standarddev FROM orders GROUP BY year

STDDEV_POP

STDDEV_POP is used to find out the standard deviation for a series of values. STDDEV_POP is used when the series of values represents the entire data population.
The standard deviation is the square root of the variance.
Use format:
STDDEV_POP(Number)
Example: Mean and standard deviation of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
STDDEV_POP(orders.totaliot) AS standarddev FROM orders GROUP BY year
Versions 17 and later

STDDEV_SAMP

STDDEV_SAMP is used find out the standard deviation for a series of values. STDDEV_SAMP is used when the series of values represents a sample of the data population. This function is equivalent to STDDEV.
The standard deviation is the square root of the variance.
Use format:
STDDEV_SAMP(Number)
Example: Mean and standard deviation of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
STDDEV_SAMP(orders.totaliot) AS standarddev FROM orders GROUP BY year
New in version 17

STDDEV_SAMP

STDDEV_SAMP is used find out the standard deviation for a series of values. STDDEV_SAMP is used when the series of values represents a sample of the data population. This function is equivalent to STDDEV.
The standard deviation is the square root of the variance.
Use format:
STDDEV_SAMP(Number)
Example: Mean and standard deviation of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
STDDEV_SAMP(orders.totaliot) AS standarddev FROM orders GROUP BY year

STDDEV_SAMP

STDDEV_SAMP is used find out the standard deviation for a series of values. STDDEV_SAMP is used when the series of values represents a sample of the data population. This function is equivalent to STDDEV.
The standard deviation is the square root of the variance.
Use format:
STDDEV_SAMP(Number)
Example: Mean and standard deviation of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
STDDEV_SAMP(orders.totaliot) AS standarddev FROM orders GROUP BY year

TAN

TAN is used to find out the tangent of a number.
Use format:
TAN(Number)

TRUNC

TRUNC is used to find out the integer part.
Use format:
TRUNC(Number)
Versions 16 and later

UNHEX

UNHEX performs the reverse operation of HEX. Each pair of hexadecimal digits:
  • is interpreted as numbers.
  • is converted into a character represented by the number.
The returned value is a binary string.
Use format:
UNHEX(Hexadecimal string)
Remark: If the characters passed to the function do not correspond to elements of an hexadecimal value, the function returns NULL.
New in version 16

UNHEX

UNHEX performs the reverse operation of HEX. Each pair of hexadecimal digits:
  • is interpreted as numbers.
  • is converted into a character represented by the number.
The returned value is a binary string.
Use format:
UNHEX(Hexadecimal string)
Remark: If the characters passed to the function do not correspond to elements of an hexadecimal value, the function returns NULL.

UNHEX

UNHEX performs the reverse operation of HEX. Each pair of hexadecimal digits:
  • is interpreted as numbers.
  • is converted into a character represented by the number.
The returned value is a binary string.
Use format:
UNHEX(Hexadecimal string)
Remark: If the characters passed to the function do not correspond to elements of an hexadecimal value, the function returns NULL.
Versions 17 and later

VARIANCE

VARIANCE is used to find out the variance for a series of values. VARIANCE is used when the series of values represents a sample of the data population. This function is equivalent to VAR_SAMP.
Use format:
VARIANCE(Number)
Example: Mean and variance of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
VARIANCE(orders.totaliot) AS myvariance FROM orders GROUP BY year
New in version 17

VARIANCE

VARIANCE is used to find out the variance for a series of values. VARIANCE is used when the series of values represents a sample of the data population. This function is equivalent to VAR_SAMP.
Use format:
VARIANCE(Number)
Example: Mean and variance of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
VARIANCE(orders.totaliot) AS myvariance FROM orders GROUP BY year

VARIANCE

VARIANCE is used to find out the variance for a series of values. VARIANCE is used when the series of values represents a sample of the data population. This function is equivalent to VAR_SAMP.
Use format:
VARIANCE(Number)
Example: Mean and variance of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
VARIANCE(orders.totaliot) AS myvariance FROM orders GROUP BY year
Versions 17 and later

VAR_POP

VAR_POP is used to find out the variance for a series of values. VAR_POP is used when the series of values represents the entire data population.
Use format:
VAR_POP(Number)
Example: Mean and variance of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
VAR_POP(orders.totaliot) AS myvariance FROM orders GROUP BY year
New in version 17

VAR_POP

VAR_POP is used to find out the variance for a series of values. VAR_POP is used when the series of values represents the entire data population.
Use format:
VAR_POP(Number)
Example: Mean and variance of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
VAR_POP(orders.totaliot) AS myvariance FROM orders GROUP BY year

VAR_POP

VAR_POP is used to find out the variance for a series of values. VAR_POP is used when the series of values represents the entire data population.
Use format:
VAR_POP(Number)
Example: Mean and variance of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
VAR_POP(orders.totaliot) AS myvariance FROM orders GROUP BY year
Versions 17 and later

VAR_SAMP

VAR_SAMP is used to find out the standard deviation for a series of values. VAR_SAMP is used when the series of values represents a sample of the data population. This function is equivalent to VARIANCE.
The standard deviation is the square root of the variance.
Use format:
VAR_SAMP(Number)
Example: Mean and variance of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
VAR_SAMP (orders.totaliot) AS myvariance FROM orders GROUP BY year
New in version 17

VAR_SAMP

VAR_SAMP is used to find out the standard deviation for a series of values. VAR_SAMP is used when the series of values represents a sample of the data population. This function is equivalent to VARIANCE.
The standard deviation is the square root of the variance.
Use format:
VAR_SAMP(Number)
Example: Mean and variance of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
VAR_SAMP (orders.totaliot) AS myvariance FROM orders GROUP BY year

VAR_SAMP

VAR_SAMP is used to find out the standard deviation for a series of values. VAR_SAMP is used when the series of values represents a sample of the data population. This function is equivalent to VARIANCE.
The standard deviation is the square root of the variance.
Use format:
VAR_SAMP(Number)
Example: Mean and variance of TotalIOT for the orders per year:
SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
VAR_SAMP (orders.totaliot) AS myvariance FROM orders GROUP BY year
See also
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment