SQL Server mathematical functions

XLeratorDB / math

Use XLeratorDB/math for a wide variety of mathematical calculations. The feature-rich XLeratorDB function library lets you include calculations in any T-SQL statement including SELECT, INSERT, UPDATE, DELETE, CREATE VIEW as well as in CTEs, stored procedures, user-defined functions, and computed columns.

XLeratorDB / math is available in two packages - XLeratorDB/math which requires SQL Server 2005 or later, and XLeratorDB/math 2008 which requires SQL Server 2008 or later.

Functions denoted with Available in XLeratorDB/math 2008 only are only available in the XLeratorDB/math 2008 package and will not install or run in SQL Server 2005. XLeratorDB 2008 packages contain functions which leverage SQL CLR features that are only available in SQL Server 2008 or later, namely 'multi-input aggregates' which provide a much easier calling syntax and typically run much faster than their scalar counterparts.

 FUNCTION REFERENCE - MATH FUNCTIONS ARITHMETIC/ALGEBRA Arithmetic Convert an integer to the text representation of that number for a given radix (base).†Available in XLeratorDB/math 2008 only Convert the text representation of a number in a given radix (base) to an integer.†Available in XLeratorDB/math 2008 only Calculate the number of combinations for a given number of items. Calculate the number of combinations with repitition of n things taken r at a time.†Available in XLeratorDB/math 2008 only Calculate the distance between 2 points on the globe, using the great circle formula. Calculate the greatest common divisor using Euclid's method. Calculate the least common multiple using Euclid's method. Calculate a number rounded up to the nearest even integer. Calculate the factorial of a number. Calculate the double factorial of a number. Calculate a number rounded up to the nearest even integer. Calculate the greatest common divisor of all the values, or only the DISTINCT values, in the expression. Calculate the nth largest distinct value in a dataset.Available in XLeratorDB/math 2008 only Calculate the nth smallest distinct value in a dataset.Available in XLeratorDB/math 2008 only Calculate a number rounded to the desired multiple. Calculate the ratio of a sum of values to the product of the factorials of those values.† Calculate the ratio of a sum of values to the product of the factorials of those values.† Calculate a number rounded up to the nearest odd integer. Calculate the value of the specified expression to the specified power. Available in XLeratorDB/math 2008 only Calculate the product of all the values, or only the DISTINCT values, in a dataset. Calculate the product of 2 decimal(38,18) values as a decimal with precision 38 and scale 18. Calculate integer portion of a division. Calculate the quotient of 2 decimal(38,18) values as a decimal with precision 38 and scale 18. Calculate the sum of a power series.† Calculate the sum of a power series.† Calculate the sum of the squares of all the values, or only the DISTINCT values, in the expression. Calculate the weighted average.Available in XLeratorDB/math 2008 only Equalities Calculate the greater of two values passed into the function. Calculate the lesser of two values passed into the function. Rounding Calculate a number rounded up, away from zero, to the nearest multiple of significance. Calculate a number rounded down, towards zero, to the nearest multiple of significance. Calculate a number rounded down, towards zero, to the nearest integer. Calculate a number rounded down, towards zero. Calculate a number rounded up, away from zero. Calculate a number truncated to an integer by removing the fractional part of the number. Formatting Calculate an Arabic numeral converted to a Roman numeral, as text. TRIGONOMETRIC Calculate the inverse hyperbolic cosine of a number. Calculate the arccotangent of a number.†Available in XLeratorDB/math 2008 only Calculate the hyperbolic arccotangent of a number.†Available in XLeratorDB/math 2008 only Calculate the inverse hyperbolic sine of a number. Calculate the inverse hyperbolic tangent of a number. Calculate the hyperbolic cosine of a number. Calculate hyperbolic cotangent of a number.†Available in XLeratorDB/math 2008 only Calculate the cosecant of the given angle. Calculate the hyperbolic cosecant of the given angle.†Available in XLeratorDB/math 2008 only Calculate the secant of the given angle. Calculate the hyperbolic secant of the given angle.†Available in XLeratorDB/math 2008 only Calculate the hyperbolic sine of a number. Calculate the hyperbolic tangent of a number. NUMERICAL INTEGRATION QUAD Evaluate an infinite integral. QUADDE Evaluate an infinite integral. QUADGK Evaluate a finite integral. QUADOSC Evaluate an infinite integral. QUADTS Evaluate a finite integral. INTERPOLATION Interpolate on a regular 2-dimensional grid.† Calculate the straight-line interpolated value of y given x in the array (x, y). Calculate the straight-line interpolated value of y given x in the array (x, y).† Interpolate on a 2-dimensional grid in 3rd normal form.Available in XLeratorDB/math 2008 only Calculate the straight-line interpolated value of y given x in the array (x, y). Calculate the interpolated value of y given x in the array (x, y).Available in XLeratorDB/math 2008 only Obtain a specific coefficient value from an approximating polynomial for a set of x- and y-values.Available in XLeratorDB/math 2008 only Calculate the coefficients of a polynomial p(x) of degree that fits the x- and y-values supplied to the function.† (table-valued function) Calculate the coefficients of a polynomial p(x) of degree n ;that fits the x- and y-values supplied to the function.† (table-valued function) Calculate the interpolated value of y given x in the array (x, y).Available in XLeratorDB/math 2008 only Calculate the goodness of fit for an approximating polynomial calculated from a series of x- and y-values for a specified number of degrees.Available in XLeratorDB/math 2008 only Calculate a new y-value given a new x-value using the coefficients of a polynomial p(x) of degree that fits the x- and y-values supplied to the function.Available in XLeratorDB/math 2008 only Calculate the interpolated value of y given x in the array (x, y). Calculate the interpolated value of y given x in the array (x, y).† MATRIX FUNCTIONS Matrix Factorization Calculate the LU factorization of an N x N matrix using partial pivoting. Calculate the LU factorization of an N x N matrix using partial pivoting.† Calculate the LU factorization of an N x N matrix using partial pivoting.† Calculate the LU factorization of an N x N matrix using partial pivoting.† Calculate the LU factorization of an N x N matrix using partial pivoting.† Return the D and V matrices representing the eigenvalues and eigenvectors of a real symmetric matrix.†Available in XLeratorDB/math 2008 only (table-valued function) Decompose a string representation of an N x N matrix into the product of an upper triangular matrix and an orthogonal matrix Q, such that A=QR. Decompose a de-normalized N x N matrix A into the product of an upper triangular matrix R and an orthogonal matrix Q, such that A = QR.† Decompose a de-normalized N x N matrix into the product of an upper triangular matrix R and an orthogonal matrix Q, such that A = QR.† Return the D and V matrices representing the eigenvalues and eigenvectors of a real symmetric matrix.†Available in XLeratorDB/math 2008 only (table-valued function) Matrix Math Return a solution to the equation A*x=b, when A is an upper-triangular matrix. Calculate a correlation matrix.† (table-valued function) Calculate a sample covariance matrix.† (table-valued function) Calculate the matrix cross-product of 2 matrices. Return a solution to the equation A*x=b, when A is a lower-triangular matrix. Add a value to every element of a matrix or to add 2 matrices of the same dimensions together. Calculate the matrix inverse of a square (N x N) matrix. Calculate the product of two matrixes or to multiply a matrix by a constant. Subtract a value from every element of a matrix or to calculate the difference between 2 matrices of the same dimensions. Calculate a correlation matrix. Calculate a sample covariance matrix. Calculate the matrix cross-product of 2 matrices.† (table-valued function) Calculate the determinant of an N x N matrix, where N specifies the number of columns in the matrix.† Calculate the determinant of an N x N matrix, where N specifies the number of columns in the matrix.† Calculate the determinant of an N x N matrix, where N specifies the number of columns in the matrix.† Calculate the determinant of an N x N matrix, where N specifies the number of columns in the matrix.† Calculate the matrix inverse of a square (N x N) matrix.† Calculate the matrix inverse of a square (N x N) matrix.† Calculate the matrix inverse of a square (N x N) matrix.† Calculate the matrix inverse of a square (N x N) matrix.† Calculate the matrix product of two arrays.† Calculate the matrix product of two arrays.† Calculate the matrix product of two arrays in third-normal form.† Calculate the matrix product of two arrays in third-normal form.† Calculate the 1-norm, 2-norm, Infinity-norm, Frobenius norm, and maximum modulus of a matrix.†Available in XLeratorDB/math 2008 only Calculate the rank of a matrix.†Available in XLeratorDB/math 2008 only Calculate the trace of a de-normalized matrix.† Calculate the trace of a de-normalized matrix.† Calculate the trace of a matrix in third-normal form.† Calculate the trace of a matrix in third-normal form.† Return the lower triangular part of the string representation of a matrix. Return the upper triangular part of the string representation of a matrix. Calculate the spectral radius of a square matrix.†Available in XLeratorDB/math 2008 only Calculate the sum of the elementwise multiplication of 1 or more matrices.†Available in XLeratorDB/math 2008 only Calculate the sum of the differences of the squares in the corresponding elements in 2 matrices.†Available in XLeratorDB/math 2008 only Calculate the sum of the sum of the squares in the corresponding elements in 2 matrices.†Available in XLeratorDB/math 2008 only Calculate the sum of the sqaure of the differences in the corresponding elements in 2 matrices.†Available in XLeratorDB/math 2008 only Calculate the economy-sized singular value decomposition of an m-x-n matrix A. Calculate the economy-sized singular value decomposition of an m-x-n matrix A.† (table-valued function) Calculate the matrix cross-product of 2 matrices. Calculate the matrix cross-product of 2 matrices.† (table-valued function) Return the transposed matrix. Matrix Randomization Generate an m-by-n matrix of pseudo-random numbers greater than or equal to zero and less than one. Generate an m-by-n matrix of pseudo-random numbers from the standard normal distribution. Generate an m-by-m random orthogonal matrix.Available in XLeratorDB/math 2008 only Matrix Helper Functions Return the vector of the diagonal of the string representation of a matrix. Generate an m-by-n identity matrix. Add columns or rows to an existing matrix variable.†Available in XLeratorDB/math 2008 only Turn table data into a string representation of a matrix, where the columns are separated by commas and the rows are separated by semi-colons.† Turn table data into a string representation of a matrix, where the columns are separated by commas and the rows are separated by semi-colons.† Find the number of columns in the string representation of a matrix. Return a column from the string representation of a matrix. Return the intersection of a row and a column from the string representation of a matrix. Return a row from the string representation of a matrix. Find the number of rows in the string representation of a matrix. Change the values in the string representation of a matrix or to perform element-wise operations on a matrix or some portion of a matrix.†Available in XLeratorDB/math 2008 only Turn third-normal form table data into a string representation of a matrix, where the columns are separated by commas and the rows are separated by semi-colons.Available in XLeratorDB/math 2008 only Turn third-normal form table data into a string representation of a matrix, where the columns are separated by commas and the rows are separated by semi-colons.† Generate an m-by-n matrix of ones. Return the Vandermonde matrix.Available in XLeratorDB/math 2008 only Generate an m-by-n matrix of zeroes. NUMBER/SERIES GENERATORS Random Number Functions Calculate a random number greater than or equal to zero and less than one. Calculate a random integer number between the numbers you specify. Calculate a pseudo-random number based on a normal distribution for the given mean and standard deviation. Random Number Generators RANDBETA Generate q sequence of random numbers from the beta distribution with two positive shape parameters alpha and beta. (table-valued function) RANDBINOM Generate a sequence of random integers from the binomial distribution for a given probability of success and a given number of trials. (table-valued function) RANDCAUCHY Generate a sequence of random numbers from a Cauchy distribution for a given location parameter and scale. (table-valued function) RANDCHISQ Generate a sequence of random numbers from a chi-squared distribution for a specified degrees of freedom. (table-valued function) RANDEXP Generate a sequence of random numbers from an exponential distribution with rate lambda. (table-valued function) RANDFDIST Generate a sequence of random numbers from an F-distribution with the degree of freedom parameters df1 and df2. (table-valued function) RANDGAMMA Generate a sequence of random numbers from a gamma distribution for the supplied shape and scale parameters. (table-valued function) RANDLAPLACE Generate a sequence of random numbers from a LaPlace distribution with supplied location and scales parameters. (table-valued function) RANDLOGISTIC Generate a sequence of random numbers from a logistic distribution of the supplied location and scale parameters. (table-valued function) RANDNORMAL Generate a sequence of random numbers from the normal distribution with mean μ and standard deviation σ. (table-valued function) RANDPOISSON Generate a sequence of random integers from the Poisson distribution for a given λ. (table-valued function) RANDSNORMAL Generate a sequence of random numbers from the standard normal distribution. (table-valued function) RANDTDIST Generate a sequence of random numbers from Student's t distribution with for the supplied degrees of freedom. (table-valued function) RANDWEIBULL Generate a sequence of random numbers from w Weibull distribution with parameters shape (λ) and scale (κ). (table-valued function) Series Generators Generate a range of integer values. (table-valued function) Generate a range of floating point values. (table-valued function) Generate a range of date values. (table-valued function) Optimization Find the minimum of a function using the Broyden-Fletcher-Goldfarb-Shanno (BFGS) method.†Available in XLeratorDB/math 2008 only Find the root of a continuous function of one variable.†Available in XLeratorDB/math 2008 only Numerical function differentiation for orders n = 1 to 4 using finite difference approximations.†Available in XLeratorDB/math 2008 only Numerically compute the gradient.†Available in XLeratorDB/math 2008 only Numerically computer the Hessian matrix.†Available in XLeratorDB/math 2008 only Numerically compute the Jacobian matrix.†Available in XLeratorDB/math 2008 only Find the root of a univariate function.†Available in XLeratorDB/math 2008 only Find the root of single-variable continuous function.†Available in XLeratorDB/math 2008 only MISC FUNCTIONS Display version information for the XLeratorDB/math module.

*!*Added in Most Recent Release of package

*BETA This function is only available in the XLeratorDB SuitePLUS-2008 Trial package as a BETA preview

This function cannot reference data from SQL Server 2014 memory-optimized tables

Pricing

Copyright 2008-2019 Westclintech LLC         Privacy Policy        Terms of Service