Functions and Operators
Functions and Operators (DML)
Basic Mathematical Operators
Operator  Description 
 Returns 
 Returns negative value of 
 Sum of 
 Difference of 
 Product of 
 Quotient ( 
Mathematical Operator Precedence
Parenthesization
Multiplication and division
Addition and subtraction
Comparison Operators
Operator  Description 
 Equals 
 Not equals 
 Greater than 
 Greater than or equal to 
 Less than 
 Less than or equal to 
 Is a value within a range 
 Is a value not within a range 
 Is a value that is null 
 Is a value that is not null 
 Compare expressions x and y. If different, return x. If they are the same, return 
 True if a value resolves to TRUE. 
 True if a value resolves to FALSE. 
Mathematical Functions
Function  Description 
 Returns the absolute value of x 
 Returns the smallest integer not less than the argument 
 Converts radians to degrees 
 Returns the value of e to the power of x 
 Returns the largest integer not greater than the argument 
 Returns the natural logarithm of x 
 Returns the natural logarithm of x 
 Returns the base10 logarithm of the specified float expression x 
 Returns the remainder of int x divided by int y 
 Returns the value of pi 
 Returns the value of x raised to the power of y 
 Converts degrees to radians 
 Rounds x to the nearest integer value, but does not change the data type. For example, the double value 4.1 rounds to the double value 4. 
 Rounds x to y decimal places 
 Returns the sign of x as 1, 0, 1 if x is negative, zero, or positive 
 Returns the square root of x. 
 Truncates x to y decimal places 
 Define equalwidth intervals (buckets) in a range between the lower boundary and the upper boundary, and returns the bucket number to which the target expression is assigned.
Expressions can be constants, column variables, or general expressions. Example Create 10 age buckets of equal size, with lower bound 0 and upper bound 100 ([0,10], [10,20]... [90,100]), and classify the age of a customer accordingly:
For example, a customer of age 34 is assigned to bucket 3 ([30,40]) and the function returns the value 3. 
Trigonometric Functions
Function  Description 
 Returns the arc cosine of x 
 Returns the arc sine of x 
 Returns the arc tangent of x 
 Returns the arc tangent of (x, y) in the range (π,π]. Equal to 
 Returns the cosine of x 
 Returns the cotangent of x 
 Returns the sine of x 
 Returns the tangent of x 
Geometric Functions
Function  Description 
 Calculates distance in meters between two WGS84 positions. 
 Converts WGS84 latitude to WGS84 Web Mercator x coordinate. 
 Converts WGS84 longitude to WGS84 Web Mercator y coordinate. 
String Functions
Function  Description 
 Decodes a BASE64encoded string. 
 Encodes a string to a BASE64encoded string. 
 Returns the number of characters in a string. Only works with unencoded fields (ENCODING set to 
 Returns the string that results from concatenating the strings specified. Note that numeric, date, timestamp, and time types will be implicitly casted to strings as necessary, so explicit casts of nonstring types to string types is not required for inputs to the concatenation operator.
Note that concatenating a variable string with a string literal, i.e. 
 Converts a noneencoded string to a transient dictionaryencoded string to allow for operations like groupby on top. When the watchdog is enabled, the number of strings that can be casted using this operator is capped by the value set with the 
 Returns the string with initial caps after any of the defined delimiter characters, with the remainder of the characters lowercased. Valid delimiter characters are 
 Returns the string of a field given by 
 Returns the dictionary key of a dictionaryencoded string column. 
 Returns the string in all lower case. Only ASCII character set is currently supported. Same as 
 Returns the leftmost number ( 
 Returns the length of a string in bytes. Only works with unencoded fields (ENCODING set to 
 Returns the string in all lower case. Only ASCII character set is currently supported. Same as 
 Leftpads the string with the string defined in 
 Removes any leading characters specified in 
 Replaces in 
 Returns the position of the first character in 
 Replace one or all matches of a substring in string
The
Use a positive Backreferences/capture groups can be used to capture and replace specific subexpressions. Use the following optional If not specified, REGEXP_REPLACE defaults to case sensitive search. 
 Search string Use Use a positive The Use optional
The If the 
 Repeats the string the number of times defined in 
 Replaces all occurrences of substring 
 Reverses the string. 
 Returns the rightmost number ( 
 Rightpads the string with the string defined in 
 Removes any trailing spaces from the string. 
 Split the string based on a delimiter 
 Tokenizes the string 
 Alias for 
 Returns a substring of The start position is 1based (that is, the first character of If If If If 
 Removes characters defined in 
TRY_CAST( str AS type)  Attempts to cast/convert a string type to any valid numeric, timestamp, date, or time type. If the conversion cannot be performed, null is returned.
Note that 
 Returns the string in uppercase format. Only ASCII character set is currently supported. Same as 
 Returns the string in uppercase format. Only ASCII character set is currently supported. Same as 
PatternMatching Functions
Name  Example  Description 

 Returns true if the string matches the pattern (casesensitive) 

 Returns true if the string does not match the pattern 

 Returns true if the string matches the pattern (caseinsensitive). Supported only when the right side is a string literal; for example, 

 Lowercase string ending with r 

 cat or hat 
Usage Notes
The following wildcard characters are supported by LIKE
and ILIKE
:
%
matches any number of characters, including zero characters._
matches exactly one character.
Date/Time Functions
Function  Description 
 Returns the current date in the GMT time zone. Example:

 Returns the current time of day in the GMT time zone. Example:

 Return the current timestamp in the GMT time zone. Same as Example:

 Returns a date after a specified time/date interval has been added. Example:

 Returns the difference between two dates, calculated to the lowest level of the date_part you specify. For example, if you set the date_part as DAY, only the year, month, and day are used to calculate the result. Other fields, such as hour and minute, are ignored. Example:

 Returns a specified part of a given date or timestamp as an integer value. Note that 'interval' must be enclosed in single quotes. Example:

 Truncates the timestamp to the specified date_part. Example:

 Returns the specified date_part from timestamp. Example:

 Adds or Subtracts count date_part units from a timestamp. Note that 'count' is enclosed in single quotes. Example:

 Return the current timestamp in the GMT time zone. Same as Example:

 Adds an interval of count date_part to timestamp or date and returns signed date_part units in the provided timestamp or date form. Example:

 Subtracts timestamp1 from timestamp2 and returns the result in signed date_part units. Example:

Supported Types
Supported date_part types:
Supported interval types:
Accepted Date, Time, and Timestamp Formats
Datatype  Formats  Examples 
DATE  YYYYMMDD  20131031 
DATE  MM/DD/YYYY  10/31/2013 
DATE  DDMONYY  31Oct13 
DATE  DD/Mon/YYYY  31/Oct/2013 
EPOCH  1383262225  
TIME  HH:MM  23:49 
TIME  HHMMSS  234901 
TIME  HH:MM:SS  23:49:01 
TIMESTAMP  DATE TIME  31Oct13 23:49:01 
TIMESTAMP  DATETTIME  31Oct13T23:49:01 
TIMESTAMP  DATE:TIME  11/31/2013:234901 
TIMESTAMP  DATE TIME ZONE  31Oct13 11:30:25 0800 
TIMESTAMP  DATE HH.MM.SS PM  31Oct13 11.30.25pm 
TIMESTAMP  DATE HH:MM:SS PM  31Oct13 11:30:25pm 
TIMESTAMP  1383262225 
Usage Notes
For twodigit years, years 6999 are assumed to be previous century (for example, 1969), and 068 are assumed to be current century (for example, 2016).
For fourdigit years, negative years (BC) are not supported.
Hours are expressed in 24hour format.
When time components are separated by colons, you can write them as one or two digits.
Months are case insensitive. You can spell them out or abbreviate to three characters.
For timestamps, decimal seconds are ignored. Time zone offsets are written as +/HHMM.
For timestamps, a numeric string is converted to +/ seconds since January 1, 1970. Supported timestamps range from 30610224000 (January 1, 1000) through 29379456000 (December 31, 2900).
On output, dates are formatted as YYYYMMDD. Times are formatted as HH:MM:SS.
Linux EPOCH values range from 30610224000 (1/1/1000) through 185542587100800 (1/1/5885487). Complete range in years: +/5,883,517 around epoch.
Statistical and Aggregate Functions
Both doubleprecision (standard) and singleprecision floating point statistical functions are provided. Singleprecision functions run faster on GPUs but might cause overflow errors.
Doubleprecision FP Function  Singleprecision FP Function  Description 
 Returns the average value of x  
 Returns the count of the number of rows returned  
 Returns the count of distinct values of x  
 Returns the approximate count of distinct values of x with defined expected error rate e, where e is an integer from 1 to 100. If no value is set for e, the approximate count is calculated using the systemwide  
 Returns the approximate median of x. Two server configuration parameters affect memory usage:
Accuracy of APPROX_MEDIAN depends on the distribution of data; see Usage Notes.  
 Returns the approximate quantile of For example,  
 Returns the maximum value of x  
 Returns the minimum value of x  
 Returns the input value if there is only one distinct value in the input; otherwise, the query fails.  
 Returns the sum of the values of x  
 Returns one sample value from aggregated column x. For example, the following query returns population grouped by city, along with one value from the state column for each group: Note: This was previously  

 Alias of CORR. Returns the coefficient of correlation of a set of number pairs. 

 Returns the coefficient of correlation of a set of number pairs. 
 Returns the number of rows satisfying the given  

 Returns the population covariance of a set of number pairs. 

 Returns the sample covariance of a set of number pairs. 

 Alias of STDDEV_SAMP. Returns sample standard deviation of the value. 

 Returns the population standard the standard deviation of the value. 

 Returns the sample standard deviation of the value. 
 Returns the sum of all expression values satisfying the given  

 Alias of VAR_SAMP. Returns the sample variance of the value. 

 Returns the population variance sample variance of the value. 

 Returns the sample variance of the value. 
Usage Notes
COUNT(DISTINCT
x
)
, especially when used in conjunction with GROUP BY, can require a very large amount of memory to keep track of all distinct values in large tables with large cardinalities. To avoid this large overhead, use APPROX_COUNT_DISTINCT.APPROX_COUNT_DISTINCT(
x
,
e
)
gives an approximate count of the value x, based on an expected error rate defined in e. The error rate is an integer value from 1 to 100. The lower the value of e, the higher the precision, and the higher the memory cost. Select a value for e based on the level of precision required. On large tables with large cardinalities, consider usingAPPROX_COUNT_DISTINCT
when possible to preserve memory. When data cardinalities permit, OmniSci uses the precise implementation ofCOUNT(DISTINCT
x
)
forAPPROX_COUNT_DISTINCT
. Set the default error rate using thehllprecisionbits
configuration parameter.The accuracy of
APPROX_MEDIAN (
x
)
upon the distribution of data. For example:For 100,000,000 integers (1, 2, 3, ... 100M) in random order, APPROX_MEDIAN can provide a highly accurate answer 5+ significant digits.
For 100,000,001 integers, where 50,000,000 have value of 0 and 50,000,001 have value of 1, APPROX_MEDIAN returns a value close to 0.5, even though the median is 1.
Currently, OmniSci does not support grouping by nondictionaryencoded strings. However, with the
SAMPLE
aggregate function, you can select nondictionaryencoded strings that are presumed to be unique in a group. For example:If the aggregated column (user_description in the example above) is not unique within a group,
SAMPLE
selects a value that might be nondeterministic because of the parallel nature of OmniSci query execution.
Miscellaneous Functions
Function  Description 
 Returns a Boolean value, with the probability of The result of the function is deterministic per row; that is, all calls of the operator for a given row return the same result. The sample ratio is probabilistic, but is generally within a thousandth of a percentile of the actual range when the underlying dataset is millions of records or larger. The following example filters approximately 50% of the rows from

UserDefined Functions
You can create your own C++ functions and use them in your SQL queries.
Userdefined Functions (UDFs) require clang++ version 9. You can verify the version installed using the command
clang++ version
.UDFs currently allow any authenticated user to register and execute a runtime function. By default, runtime UDFs are globally disabled but can be enabled with the runtime flag
enableruntimeudf
.
Create your function and save it in a .cpp file; for example, /var/lib/omnisci/udf_myFunction.cpp.
Add the UDF configuration flag to omnisci.conf. For example:
Use your function in a SQL query. For example:
Sample UserDefined Function
This function, udf_diff.cpp, returns the difference of two values from a table.
Code Commentary
Include the standard integer library, which supports the following datatypes:
bool
int8_t (cstdint), char
int16_t (cstdint), short
int32_t (cstdint), int
int64_t (cstdint), size_t
float
double
void
The next four lines are boilerplate code that allows OmniSci to determine whether the server is running with GPUs. OmniSci chooses whether it should compile the function inline to achieve the best possible performance.
The next line is the actual userdefined function, which returns the difference between INTEGER values x and y.
To run the udf_diff
function, add this line to your /var/lib/omnisci/omnisci.conf file (in this example, the .cpp file is stored at /var/lib/omnisci/udf_diff.cpp):
Restart the OmniSci server.
Use your command from an OmniSci SQL client to query, for example, a table named myTable that contains the INTEGER columns myInt1
and myInt2
.
OmniSci returns the difference as an INTEGER value.
Last updated