SQL functions and operators

ReOrc's semantic layer translates user-defined queries into the SQL dialect of your underlying data source. However, due to differences in SQL syntax and the need for query transformation, certain SQL functions and operators may not be fully supported—particularly within query fragments that perform SELECT operations directly from tables.

To ensure compatibility and consistent behavior, ReOrc provides a defined set of supported SQL functions and expressions. Below is the list of currently supported functions:

<	Comparison	Returns TRUE if the first value is less than the second
>	Comparison	Returns TRUE if the first value is greater than the second
<=	Comparison	Returns TRUE if the first value is less than or equal to the second
>=	Comparison	Returns TRUE if the first value is greater than or equal to the second
=	Comparison	Returns TRUE if the first value is equal to the second
<> or !=	Comparison	Returns TRUE if the first value is not equal to the second
ABS	Mathematical functions	Absolute value
CEIL	Mathematical functions	Nearest integer greater than or equal to argument
DEGREES	Mathematical functions	Converts radians to degrees
EXP	Mathematical functions	Exponential (e raised to the given power)
FLOOR	Mathematical functions	Nearest integer less than or equal to argument
LN	Mathematical functions	Natural logarithm
LOG	Mathematical functions	Base 10 logarithm
LOG10	Mathematical functions	Base 10 logarithm (same as LOG)
PI	Mathematical functions	Approximate value of π
POWER	Mathematical functions	a raised to the power of b
RADIANS	Mathematical functions	Converts degrees to radians
ROUND	Mathematical functions	Rounds v to s decimal places
SIGN	Mathematical functions	Sign of the argument (-1, 0, or +1)
SQRT	Mathematical functions	Square root
TRUNC	Mathematical functions	Truncates to integer (towards zero)
ACOS	Trigonometric functions	Inverse cosine, result in radians
ASIN	Trigonometric functions	Inverse sine, result in radians
ATAN	Trigonometric functions	Inverse tangent, result in radians
ATAN2	Trigonometric functions	Inverse tangent of y/x, result in radians
COS	Trigonometric functions	Cosine, argument in radians
COT	Trigonometric functions	Cotangent, argument in radians
SIN	Trigonometric functions	Sine, argument in radians
TAN	Trigonometric functions	Tangent, argument in radians
||	String functions	Concatenates two strings
BTRIM	String functions	Removes characters from start and end of string
BIT_LENGTH	String functions	Returns number of bits in the string
CHAR_LENGTH	String functions	Returns number of characters in the string
LOWER	String functions	Converts the string to all lower case
LTRIM	String functions	Removes characters from the start of string
OCTET_LENGTH	String functions	Returns number of bytes in the string
POSITION	String functions	Returns index of substring within string
RTRIM	String functions	Removes characters from the end of string
SUBSTRING	String functions	Extracts the substring of string
TRIM	String functions	Removes characters from start, end, or both ends
UPPER	String functions	Converts the string to all upper case
ASCII	String functions	Returns the numeric code of the first character
CONCAT	String functions	Concatenates all arguments
LEFT	String functions	Returns first n characters (or all but last ABS(n))
REPEAT	String functions	Repeats string the specified number of times
REPLACE	String functions	Replaces all occurrences of a substring
RIGHT	String functions	Returns last n characters (or all but first ABS(n))
STARTS_WITH	String functions	Returns TRUE if string starts with prefix
LIKE	Pattern matching	Returns TRUE if the string matches the pattern
REGEXP_SUBSTR	Pattern matching	Returns substring matching regex pattern
TO_CHAR	Data type	Converts timestamp to string by format
DATE_ADD	Date/time functions	Adds interval to a timestamp
DATE_TRUNC	Date/time functions	Truncates timestamp to specified precision
DATEDIFF	Date/time functions	Difference between date parts of expressions
EXTRACT	Pattern matching	Retrieves subfields like year or hour
LOCALTIMESTAMP	Date/time functions	Returns current date and time without time zone
NOW	Date/time functions	Returns current date and time with time zone
CASE	Conditional expressions	Generic conditional expression
COALESCE	Conditional expressions	Returns the first non-NULL argument
NULLIF	Conditional expressions	Returns NULL if arguments are equal
GREATEST	Conditional expressions	Selects the largest value
LEAST	Conditional expressions	Selects the smallest value
AVG	Aggregate functions	Average of non-NULL input values
COUNT	Aggregate functions	Counts non-NULL input rows
COUNT(DISTINCT)	Aggregate functions	Counts unique non-NULL input values
MAX	Aggregate functions	Maximum of non-NULL input values
MIN	Aggregate functions	Minimum of non-NULL input values
SUM	Aggregate functions	Sum of non-NULL input values
MEASURE	Aggregate functions	Works with measures of any type
COVAR_POP	Aggregate functions	Population covariance
COVAR_SAMP	Aggregate functions	Sample covariance
STDDEV_POP	Aggregate functions	Population standard deviation
STDDEV_SAMP	Aggregate functions	Sample standard deviation
VAR_POP	Aggregate functions	Population variance
VAR_SAMP	Aggregate functions	Sample variance
IN	Row and array	TRUE if value matches any from a list
NOT IN	Row and array	TRUE if value matches none from a list