Firebird Documentation IndexFirebird 3.0.1 Release NotesData Manipulation Language (DML) → Internal Functions
Firebird Home Firebird Home Prev: Advanced Plan OutputFirebird Documentation IndexUp: Data Manipulation Language (DML)Next: DML Improvements

Internal Functions

SUBSTRING with Regular Expressions
Inverse Hyperbolic Trigonometric Functions
Statistical Functions
TRIM() BLOB Arguments Lose 32 KB limit
String Literal Limit Adjustments
Enhancements to DATEADD() Internal Function

Additions and enhancements to the internal functions set are:

SUBSTRING with Regular Expressions

Adriano dos Santos Fernandes

A substring search can now use a regular expression.

Search Pattern

  SUBSTRING(<string> [NOT] SIMILAR TO <pattern> ESCAPE <char>)
        

Discussion: TrackerCORE-2006

For more information about the use of SIMILAR TO expressions, refer to README.similar_to.txt in the /doc/ subdirectory of your Firebird installation.

Tip

The regex used is the SQL one. A guide is available in the DML chapter of the v.2.5 release notes and also at the Firebird web site.

Inverse Hyperbolic Trigonometric Functions

Claudio Valderrama C.

The six inverse hyperbolic trigonometric functions have been implemented internally. They are:

ACOSH

Returns the hyperbolic arc cosine of a number (expressed in radians). Format: ACOSH( <number> )

ASINH

Returns the hyperbolic arc sine of a number (expressed in radians). Format: ASINH( <number> )

ATANH

Returns the hyperbolic arc tangent of a number (expressed in radians). Format: ATANH( <number> )

COSH

Returns the hyperbolic cosine of an angle (expressed in radians). Format: COSH( <number> )

SINH

Returns the hyperbolic sine of an angle (expressed in radians). Format: SINH( <number> )

TANH

Returns the hyperbolic tangent of an angle (expressed in radians). Format: TANH( <number> )

Statistical Functions

Hajime Nakagami

Adriano dos Santos Fernandes

A suite of SQL-standards-compliant statistical functions has been implemented.

Aggregate Statistical Functions

Functions comprise Sample Variance, Population Variance, Sample Standard Deviation, Population Standard Deviation, Sample Population, Population Covariance and Coefficient of Correlation. See Tracker ticket CORE-4717.

Syntax

  <single param statistical function> ::= <single param statistical function name>(<expr>)
  <single param statistical function name> := { VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP }

  <dual param statistical function> ::= <dual param statistical function name>(<expr1>, <expr>>)
  <dual param statistical function name> := { COVAR_POP | COVAR_SAMP | CORR }
        

Semantics

  • NULL is returned from VAR_SAMP, STDDEV_SAMP or COVAR_SAMP if the result count is 0 or 1
  • NULL is returned from VAR_POP, STDDEV_POP, COVAR_POP or CORR if the result count is 0

Syntax

SELECT STDDEV_SAMP(salary) FROM employees;
        

SD/Variance Function Descriptions

Function Format Description
VAR_SAMP VAR_SAMP( <expr> ) Returns the Sample Variance, equivalent to
(SUM(<expr> ^ 2) - SUM(<expr>) ^ 2 / COUNT(<expr>)) / (COUNT(<expr>) - 1)
                
VAR_POP VAR_POP( <expr> ) Returns the the Population Variance, equivalent to
(SUM(<expr> ^ 2) - SUM(<expr>) ^ 2 / COUNT(<expr>)) / COUNT(<expr>)
                
STDDEV_SAMP STDDEV_SAMP( <expr> ) Returns the Sample Standard Deviation, equivalent to
SQRT(VAR_SAMP(<expr>))
                
STDDEV_POP STDDEV_POP( <expr> ) Returns the Population Standard Deviation, equivalent to
SQRT(VAR_POP(<expr>))
                
COVAR_SAMP COVAR_SAMP( <expr1>, <expr2> ) Returns the Sample Population, equivalent to
(SUM(<expr1> * <expr2>) - SUM(<expr1>) * SUM(<expr2>) / COUNT(*)) / (COUNT(*) - 1)
                
COVAR_POP COVAR_POP( <expr1>, <expr2> ) Returns the Population Covariance, equivalent to
(SUM(<expr1> * <expr2>) - SUM(<expr1>) * SUM(<expr2>) / COUNT(*)) / COUNT(*)
                
CORR CORR( <expr1>, <expr2> ) Returns the Coefficient of Correlation, equivalent to
COVAR_POP(<expr1>, <expr2>) / (STDDEV_POP(<expr2>) * STDDEV_POP(<expr1>))
                

Linear Regression Functions

The suite of REGR_* functions analyses the relationships between two sets of numeric data, considering only sets that are not NULL in either expression. See Tracker ticket CORE-4722.

Syntax

<regr function> ::= <function name>(<expr1>, <expr2>)
<function name> := { REGR_AVGX | REGR_AVGY | REGR_COUNT | REGR_INTERCEPT |
                         REGR_R2 | REGR_SLOPE | REGR_SXX | REGR_SXY | REGR_SYY }
        

Formulae

The formulae use the following variables:

  Y: <expr1> (<expr1> IS NOT NULL AND <expr2> IS NOT NULL)
  X: <expr2> (<expr1> IS NOT NULL AND <expr2> IS NOT NULL)
  N: COUNT of recordset unless <expr1> IS NULL OR <expr2> IS NULL
        

Important

Y and X are DOUBLE PRECISION. N is SMALLINT, INTEGER or BIGINT.

All functions eliminate expression pairs where either expression in the pair is NULL. If no rows remain, the functions (except REGR_COUNT()) return NULL.

Linear Regression Function Descriptions

Function Format Description
REGR_AVGX REGR_AVGX(Y, X) Returns the average of the independent expression (Y) in the expression pair. The return value is of type DOUBLE PRECISION.
Formula: REGR_AVGX(Y, X) = SUM(X) / N
                
REGR_AVGY REGR_AVGY(Y, X) Returns the average of the dependent expression (X) in the expression pair. The return value is of type DOUBLE PRECISION.
Formula: REGR_AVGY(Y, X) = SUM(Y) / N
                
REGR_COUNT REGR_COUNT(Y, X) Returns the number of expression pairs (Y and X). The return value is of type SMALLINT, INTEGER or BIGINT. If no rows remain after elimination of pairs where either expression is NULL, the function returns 0.
Formula: REGR_COUNT(Y, X) = N
                
REGR_INTERCEPT REGR_INTERCEPT(Y, X) Returns the y-intercept of the regression line determined by a set of expression pairs (Y and X).
Formula: REGR_INTERCEPT(Y, X) = REGR_AVGY(Y, X) - REGR_SLOPE(Y, X) * REGR_AVGX(Y, X)
                
REGR_R2 REGR_R2(Y, X) Returns the square of the correlation coefficient of a set of expression pairs (Y and X).
Formula: REGR_R2(Y, X) = POWER(CORR(Y, X),2)
                
REGR_SLOPE REGR_SLOPE(Y, X) Returns the slope of the regression line, determined by a set of expression pairs (Y and X).
Formula: REGR_SLOPE(Y, X) = COVAR_POP(Y, X) / VAR_POP(X)
                
REGR_SXX REGR_SXX(Y, X) Returns the sum of squares of the independent expression (Y) in an expression pair (Y and X).
Formula: REGR_SXX(Y, X) = N * VAR_POP(X)
                
REGR_SXY REGR_SXY(Y, X) Returns the sum of products of the independent expression multiplied by the dependent expression in an expression pair (Y and X).
Formula: REGR_SXY(Y, X) = N * COVAR_POP(Y, X)
                
REGR_SYY REGR_SYY(Y, X) Returns the sum of squares of the dependent expression in an expression pair (Y and X).
Formula: REGR_SYY(Y, X) = N * VAR_POP(Y)
                

TRIM() BLOB Arguments Lose 32 KB limit

Adriano dos Santos Fernandes

In prior versions, TRIM(substring from string) allowed BLOBs for both arguments, but the first argument had to be smaller than 32 KB. Now both arguments can take BLOBs of up to 4 GB.

String Literal Limit Adjustments

Adriano dos Santos Fernandes

The internal length of a string can, at some levels, be almost 64 KB. Tests demonstrated that it is safe to accept a string literal of up to that size for writing to a text BLOB. Accordingly,

  1. The (32KB - 3) “safety limit” on literal string length for writing to text BLOBs has been raised to 65,533 bytes (64KB - 3);
  2. A limit, in characters, is calculated in run-time for strings that are in multi-byte character sets, to avoid overrunning the bytes limit. For example, for a UTF8 string (max. 4 bytes/character) the run-time limit is likely to be about (floor (65533/4)) = 16383 characters.

See Tracker ticket CORE-4881.

Enhancements to DATEADD() Internal Function

Adriano dos Santos Fernandes

For the internal function DATEADD()

  • The function now supports a fractional value for MILLISECOND. See Tracker item CORE-4457.

  • the data type of input <amount> arguments has changed from INTEGER to BIGINT. See Tracker item CORE-4310.

Prev: Advanced Plan OutputFirebird Documentation IndexUp: Data Manipulation Language (DML)Next: DML Improvements
Firebird Documentation IndexFirebird 3.0.1 Release NotesData Manipulation Language (DML) → Internal Functions