Functions

The Sybase CEP is capable of utilizing a variety of function components.

• Scalar Functions
  Scalar Functions take a list of scalar arugments and return a single scalar value.
• Aggregate Functions
  Aggregate functions return a single result row based on input consisting of groups of rows.
• Mathematical Formulas for Aggregate Functions
  Two tables provide the equivalent mathematical formulas for several of the aggregate functions supported in Sybase CEP.
• ABS()
  Scalar. Returns the absolute value of an expression.
• ACOS()
  Scalar. Returns the arccosine of a value.
• ASCII()
  Scalar. Returns the Unicode code point for a particular character.
• ASIN()
  Scalar. Returns the arcsine of a value.
• ATAN()
  Scalar. Returns the arctangent of a value.
• ATAN2()
  Scalar. Returns the arctangent of the quotient of two values.
• AVG()
  Aggregate. Returns the average value of an expression, calculated over the rows in a window
• AVG()
  Scalar. Returns the average value of multiple expressions.
• BITAND()
  Scalar. Returns the results of performing a bitwise AND operation on two expressions.
• BITCLEAR()
  Scalar. Returns the value of an expression after setting a specific bit to zero.
• BITFLAG() function
  Scalar. Returns a value with all bits set to zero, except for specified bits.
• BITMASK()
  Scalar. Returns a value with all bits set to zero except for a specified range of bits.
• BITNOT()
  Scalar. Returns the value of an expression with all bits inverted (zeroes set to one and vice-versa).
• BITOR()
  Scalar. Returns the results of performing a bitwise OR operation on two expressions.
• BITSET()
  Scalar. Returns the value of an expression after setting a specific bit to one.
• BITSHIFTLEFT()
  Scalar. Returns the value of an expression after shifting the bits left a specific number of positions.
• BITSHIFTRIGHT()
  Scalar. Returns the value of an expression after shifting the bits right a specific number of positions.
• BITTEST()
  Scalar. Returns the value of a specific bit in a binary value.
• BITTOGGLE()
  Scalar. Returns the value of an expression after inverting the value of a specific bit.
• BITXOR()
  Scalar. Returns the results of performing a bitwise exclusive or operation on two expressions.
• CEIL()
  Scalar. Returns the smallest integer value greater than or equal to a specific value.
• CHR() or CHAR()
  Scalar. Returns the characters responding to one or more Unicode code points.
• COALESCE()
  Other. Returns the first non-NULL expression from a list of expressions.
• CORR()
  Aggregate. Returns the correlation coefficient of a set of number pairs. The CORR function is an alias of the CORRCOEF() function.
• COS()
  Scalar. Returns the cosine of a value.
• COSD()
  Scalar. Returns the cosine of a value, expressed in degrees.
• COSH()
  Scalar. Returns the hyperbolic cosine of a value.
• COUNT()
  Aggregate. Returns the number of rows containing a non-NULL value in a particular column.
• COVAR_POP()
  Aggregate. Returns the population covariance of a set of number pairs.
• COVAR_SAMP()
  Aggregate. Returns the sample covariance of a set of number pairs.
• DATECEILING()
  Scalar. Computes a new timestamp based on the provided timesamp, multiple and date_part arguments, with subordinate parts set to zero. The result is then rounded up to the minimum date_part multiple that is greater than or equal to the input timestamp.
• DATEFLOOR()
  Scalar. Computes a new timestamp based on the provided timestamp, multiple and date_part arguments, with subordinate parts set to zero. The result is then rounded down to the maximum date_part multiple that is less than or equal to the input timestamp.
• DATEROUND()
  Scalar. Computes a new timestamp based on the provided timestamp, multiple and date_part arguments, with subordinate date_parts set to zero. The result is then rounded to the value of a date_part multiple that is nearest to the input timestamp.
• DAYOFMONTH()
  Scalar. Returns an integer representing the day of the month as extracted from a timestamp value.
• DAYOFWEEK()
  Scalar. Returns an integer representing the day of the week (1 is Sunday) as extracted from a timestamp value.
• DAYOFYEAR()
  Scalar. Returns an integer representing the day of the year (one-based) as extracted from a timestamp value.
• DISTANCE()
  Scalar. Returns a number representing the distance between two points in either two or three dimensions.
• DISTANCESQUARED()
  Scalar. Returns a number representing the square of the distance between two points in either two or three dimensions.
• EXP()
  Scalar. Returns a number representing the value of e raised to a specific exponent.
• EXP_WEIGHTED_AVG()
  Aggregate. The EXP_WEIGHTED_AVG function calculates an exponential weighted average.
• EXTRACT()
  Scalar. Returns a subset of the bytes from a BLOB value.
• FIRST()
  Other. Returns the value of a column from a specific row in a window.
• FIRST_VALUE()
  Aggregate. Returns first value from an ordered set of values. FIRST_VALUE() is an alias for the FIRST() function in the Sybase CEP compiler.
• FLOOR()
  Scalar. Returns the largest integer value less than or equal to a specific value.
• GET___COLUMNBYNAME()
  Other. Returns the value of a column identified by an expression evaluated at runtime.
• GETPREFERENCE___()
  Other. Returns the value of a preference as set in the configuration file c8-server.conf.
• GETTIMESTAMP()
  Scalar. Returns the timestamp of a row.
• HOUR()
  Scalar. Returns an integer representing the hour of the day (0 to 23) as extracted from a timestamp value
• INSTR()
  Scalar. Returns the starting position (one-based) of a string within another string.
• LAST()
  Other. Returns the value of a column from a specific row in a window.
• LAST_VALUE()
  Aggregate. Returns the last value from an ordered set of values. LAST_VALUE() is an alias for the LAST() function in the Sybase CEP compiler.
• LEFT()
  Scalar. Returns a specified number of characters from the beginning of a given string.
• LENGTH()
  Scalar. Returns the length of a given string or BLOB, in characters or bytes, respectively.
• LN()
  Scalar. Returns the natural logarithm of a given value.
• LOG()
  Scalar. Returns the logarithm of a given value to a specified base.
• LOG10()
  Scalar. Returns the logarithm of a given value to the base 10.
• LOG2()
  Scalar. Returns the logarithm of a given value to the base 2.
• LOWER()
  Scalar. Returns a given string with all characters converted to lower case.
• LTRIM()
  Scalar. Returns a given string with all leading spaces removed.
• MAKETIMESTAMP()
  Scalar. Constructs a timestamp.
• MAX()
  Aggregate. Returns the maximum value of a given expression over multiple rows.
• MAX()
  Scalar. Returns the maximum value from a list of expressions.
• MEANDEVIATION()
  Aggregate. Returns the mean absolute deviation (the mean of the absolute value of the deviations from the mean of all values) of a given expression over multiple rows.
• MEDIAN()
  Aggregate. Returns the median value of a given expression over multiple rows.
• MICROSECOND()
  Scalar. Returns an integer representing the microsecond as extracted from a timestamp value.
• MID()
  Scalar. Returns a range of characters from a given string.
• MIN()
  Aggregate. Returns the minimum value of a given expression over multiple rows.
• MIN()
  Scalar. Returns the minimum value from a list of expressions.
• MINUTE()
  Scalar. Returns an integer representing the minute as extracted from a timestamp value.
• MOD()
  Scalar. Returns the remainder of dividing one expression by another.
• MONTH()
  Scalar. Returns an integer representing the month as extracted from a timestamp value.
• NEXTVAL()
  Scalar. The first call to this function returns 1, and then each subsequent call returns a value larger than that returned by the previous call.
• NOW()
  Scalar. Returns a timestamp corresponding to the current time. Note that this function is lower resolution than Highresolutionnow(), but users considerable fewer resources. Use this version when performance is a higher priority than resolution.
• PI()
  Scalar. Returns an approximation of the constant pi.
• POWER()
  Scalar. Returns the value of a given base raised to a specified exponent.
• PREV()
  Other. Returns the value of a given column from a prior row.
• RANDOM()
  Scalar. Returns a random value greater than or equal to 0 and less than 1.
• REGEXP_FIRSTSEARCH()
  Scalar. Returns the first occurrence of a POSIX regular expression pattern found in a given string.
• REGEXP_REPLACE()
  Scalar. Returns a given string with the first occurrence of a match for a POSIX regular expression pattern replaced with a second, specified string.
• REGEXP_SEARCH()
  Scalar. Determines whether or not a string contains a match for a POSIX regular expression pattern.
• REGR_AVGX()
  Aggregate. Computes the average of the independent variable of the regression line.
• REGR_AVGY()
  Aggregate. Computes the average of the dependent variable of the regression line.
• REGR_COUNT()
  Aggregate. Returns an integer that represents the number of non-NULL number pairs used to fit the regression line.
• REGR_INTERCEPT()
  Aggregate. Computes the y-intercept of the linear regression line that best fits the dependent and independent variables.
• REGR_R2()
  Aggregate. Computes the coefficient of determination (also referred to as R-squared or the goodness of fit statistic) for the regression line.
• REGR_SLOPE()
  Aggregate. Computes the slope of the linear regression line fitted to non-NULL pairs.
• REGR_SXX()
  Aggregate. Returns the sum of squares of the independent expressions used in a linear regression model. Use the REGR_SXX function to evaluate the statistical validity of a regression model.
• REGR_SXY()
  Aggregate. Returns the sum of products of the dependent and independent variables. The REGR_SXY function can be used to evaluate the statistical validity of a regression model.
• REGR_SYY()
  Aggregate. Returns values that can evaluate the statistical validity of a regression model.
• REPLACE()
  Scalar. Returns a given string with every instance of a specified substring replaced with another specified string.
• RIGHT()
  Scalar. Returns a specified number of characters from the end of a given string.
• ROUND()
  Scalar. Returns a given value rounded to a specified number of decimal places.
• RTRIM()
  Scalar. Returns a specified string after stripping trailing spaces.
• SECOND()
  Scalar. Returns an integer representing the second as extracted from a timestamp value.
• SIGN()
  Scalar. Determines whether a given value is positive or negative.
• SIN()
  Scalar. Returns the sine, in radians, of a given value.
• SIND()
  Scalar. Returns the sine, in degrees, of a given value.
• SINH()
  Scalar. Returns the hyperbolic sine of a given value.
• SQRT()
  Scalar. Returns the square root of a given value.
• STDDEV()
  Aggregate. Computes the standard deviation of a sample consisting of a numeric-expression, as a DOUBLE. Alias for STDDEV_SAMP().
• STDDEVIATION()
  Aggregate. Returns the standard deviation of a given expression over multiple rows.
• STDDEV_POP()
  Aggregate. Computes the standard deviation of a population consisting of a numeric-expression, as a DOUBLE.
• STDDEV_SAMP()
  Aggregate. Computes the standard deviation of a sample consisting of a numeric-expression, as a DOUBLE.
• SUBSTR()
  Scalar. Returns a specified number of characters from a specific position within a given string.
• SUM()
  Aggregate. Returns the sum of the values of a given expression over multiple rows.
• TAN()
  Scalar. Returns the tangent, in radians, of a given value.
• TAND()
  Scalar. Returns the tangent, in degrees, of a given value.
• TANH()
  Scalar. Returns the hyperbolic tangent of a given value.
• THRESHOLD()
  Scalar. Determines whether or not the value in a given column crosses a specified threshold.
• TO_BLOB()
  Scalar. Converts a given string to a BLOB value.
• TO_BOOLEAN()
  Scalar. Converts a given string to a Boolean value
• TO_FLOAT()
  Scalar. Converts a given value to a floating point value.
• TO_INTEGER()
  Scalar. Converts a given value to an Integer value.
• TO_INTERVAL()
  Scalar. Converts a given value to an Interval.
• TO_LONG()
  Scalar. Converts a given value to a Long.
• TO_STRING()
  Scalar. Converts a given value to a String.
• TO_TIMESTAMP()
  Scalar. Converts a given value to a Timestamp.
• XMLPARSE() and TO_XML() functions
  Scalar. Converts (casts) a string expression to XML.
• TRIM()
  Scalar. Returns a given string after removing leading and trailing spaces.
• UPPER()
  Scalar. Returns a given string with all characters converted to upper case.
• USERNAME()
  Scalar. Returns the user name of the owner of the current query module file. Only valid if the Sybase CEP Server is configured for user authentication.
• VARIANCE()
  Aggregate. Computes the statistical variance of a sample consisting of a numerical-expression, as a DOUBLE. The VARIANCE() function is an alias for VAR_SAMP(); the alias is a vendor extension and not specified by ANSI SQL.
• VAR_POP()
  Aggregate. Computes the statistical variance of a population consisting of a numeric-expression, as a DOUBLE.
• VAR_SAMP()
  Aggregate. Computes the statistical variance of a sample consisting of a numeric-expression, as a DOUBLE.
• VWAP()
  Aggregate. The VWAP function computes a volume-weighted average price for an instrument.
• WEIGHTED_AVG()
  Aggregate. Calculates an arithmetically (or linearly) weighted average.
• XMLAGG()
  Aggregate. Returns a single XML result aggregated over a group of rows.
• XMLATTRIBUTES()
  Scalar. Appends one or more attributes to an XML element being constructed with XMLELEMENT().
• XMLCOMMENT()
  Scalar. Appends a comment to an xml element being constructed with XMLELEMENT().
• XMLCONCAT()
  Scalar. Concatenates XML expressions.
• XMLDELETE()
  Scalar. Deletes element trees from an XML value.
• XMLELEMENT()
  Scalar. Constructs an XML element tree.
• XMLEXISTS()
  Scalar. Determines whether or not the results of an XPATH expression exists in an XML element.
• XMLEXTRACT()
  Scalar. Extracts the portion of an XML value that matches a given XPATH expression.
• XMLEXTRACTVALUE()
  Scalar. Extracts the value of an XML element tree that matches a given XPATH expression.
• XMLINSERT()
  Scalar. Inserts one XML value into another XML value.
• XMLPATTERNMATCH()
  Aggregate. Generates an XML tree contianing all matches detected by a pattern-matching query.
• XMLPI()
  Scalar. Adds a processing instruction node to an XML element being constructed with XMLELEMENT().
• XMLSERIALIZE()
  Scalar. Converts an XML value to a String. Note that this function is the same as TO_STRING() with an XML parameter.
• XMLTRANSFORM()
  Scalar. Applies and XSL transformation (XSLT) to an XML value.
• XMLUPDATE()
  Scalar. Replaces sections of an XML value matching a given XPATH string with another XML value.
• XPATH()
  Scalar. Returns the value in the string representation of an XML element matching an XPATH expression.
• YEAR()
  Scalar. Returns an integer representing the year as extracted from a timestamp value.