SQL Expressions
The following tables outline the SQL functions that can be used in NetSuite saved search formulas and custom formula fields:
- Numeric Functions
- Character Functions Returning Character Values
- Character Functions Returning Number Values
- Datetime Functions
- NULL-Related Functions
- Decode
- Sysdate
- CASE
- Aggregate Functions
Important
This section provides some material for convenience only. It is assumed that you are familiar with SQL expressions.
Note: not all SQL expressions are supported in NetSuite.
Numeric Functions
Function | Syntax | Description |
---|---|---|
ABS | ABS(n) | returns the absolute value of n |
ACOS | ACOS(n) | returns the arc cosine of n |
ASIN | ASIN(n) | returns the arc sine of n |
ATAN | ATAN(n) | returns the arc tangent of n |
ATAN2 | ATAN2(n1 { , | / } n2) | returns the arc tangent of n1 and n2 |
BITAND | BITAND(expr1, expr2) | computes an AND operation on the bits of expr1 and expr2 |
CEIL | CEIL(n) | returns smallest integer greater than or equal to n |
COS | COS(n) | returns the cosine of n |
COSH | COSH(n) | returns the hyperbolic cosine of n. |
EXP | EXP(n) | returns e raised to the nth power |
FLOOR | FLOOR(n) | returns largest integer equal to or less than n. |
LN | LN(n) | returns the natural logarithm of n, |
LOG | LOG(n2, n1) | returns the logarithm, base n2, of n1 |
MOD | MOD(n2, n1) | returns the remainder of n2 divided by n1 |
NANVL | NANVL(n2, n1) | returns an alternative value n1 if the input value n2 is not a number |
POWER | POWER(n2, n1) | returns n2 raised to the n1 power |
REMAINDER | REMAINDER(n2, n1) | returns the remainder of n2 divided by n1 |
ROUND (number) | ROUND(n [, integer ]) | returns n rounded to integer places to the right of the decimal point |
SIGN | SIGN(n) | returns the sign of n |
SIN | SIN(n) | returns the sine of n |
SINH | SINH(n) | returns the hyperbolic sine of n |
SQRT | SQRT(n) | returns the square root of n |
TAN | TAN(n) | returns the tangent of n |
TANH | TANH(n) | returns the hyperbolic tangent of n |
TRUNC (number) | TRUNC(n1 [, n2 ]) | returns n1 truncated to n2 decimal places |
Character Functions Returning Character Values
Function | Syntax | Description |
---|---|---|
CHR | CHR(n [ USING NCHAR_CS ]) | returns the character having the binary equivalent to n as a VARCHAR2 value |
CONCAT | CONCAT(char1, char2) | returns char1 concatenated with char2 |
INITCAP | INITCAP(char) | returns char, with the first letter of each word in uppercase, all other letters in lowercase |
LOWER | LOWER(char) | returns char, with all letters lowercase |
LPAD | LPAD(expr1, n [, expr2 ]) | returns expr1, left-padded to length n characters with the sequence of characters in expr2 |
LTRIM | LTRIM(char [, set ]) | removes from the left end of char all of the characters contained in set |
REGEXP_REPLACE |
|
allows you to search a string for a regular expression pattern |
REGEXP_SUBSTR |
|
allows you to search a string for a regular expression pattern |
REPLACE |
|
returns char with every occurrence of search_string replaced with replacement_string |
RPAD | RPAD(expr1 , n [, expr2 ]) | returns expr1, right-padded to length n characters with expr2, replicated as many times as necessary |
RTRIM | RTRIM(char [, set ]) | removes from the right end of char all of the characters that appear in set |
SOUNDEX | SOUNDEX(char) | returns a character string containing the phonetic representation of char |
SUBSTR |
|
return a portion of char, beginning at character position, substring_length characters long |
TRANSLATE | TRANSLATE(expr, from_string, to_string) | returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string |
TREAT | TREAT(expr AS [ REF ] [ schema. ]type) | changes the declared type of an expression |
TRIM |
|
allows you to trim leading or trailing characters (or both) from a character string |
UPPER | UPPER(char) | returns char, with all letters uppercase |
Character Functions Returning Number Values
Function | Syntax | Short Description |
---|---|---|
ASCII | ASCII(char) | returns the decimal representation in the database character set of the first character of char |
INSTR |
|
searches string for substring |
LENGTH |
|
returns the length of char |
REGEXP_INSTR |
|
allows you search a string for a regular expression pattern |
TO_NUMBER() | TO_NUMBER(expr [, fmt [, ‘nlsparam’ ] ]) | converts a formatted TEXT or NTEXT expression to a number |
Datetime Functions
Function | Syntax | Short Description |
---|---|---|
ADD_MONTHS | ADD_MONTHS(date, integer) | returns the date plus integer months |
LAST_DAY | LAST_DAY(date) | returns the date of the last day of the month that contains date |
NEXT_DAY | NEXT_DAY(date, char) | returns the date of the first weekday named by char that is later than the date |
ROUND (DATE) | ROUND(date [, fmt ]) | returns date rounded to the unit specified by the format model fmt |
TO_CHAR() | TO_CHAR({ datetime | interval } [, fmt [, ‘nlsparam’ ] ]) | converts a datetime or interval value to a value of VARCHAR2 datatype in the format specified by the date format fmt |
TO_DATE() | TO_DATE(char [, fmt [, ‘nlsparam’ ] ]) | converts a formatted TEXT or NTEXT expression to a DATETIME value |
TRUNK (DATE) | TRUNC(date [, fmt ]) | returns date with the time portion of the day truncated to the unit specified by the format model fmt |
NULL-Related Functions
Function | Syntax | Short Description |
---|---|---|
COALESCE | COALESCE(expr [, expr ]…) | returns the first non-null expr in the expression list |
NULLIF | NULLIF(expr1, expr2) | compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. |
NVL | NVL(expr1, expr2) | allows you to replace null with the second parameter |
NVL2 | NVL2(expr1, expr2, expr3) | If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3. |
Decode
Function | Syntax | Short Descriptions |
---|---|---|
DECODE |
|
Compares expr to each search value one by one. If expr is equal to a search, the corresponding result is returned. If no match is found, default is returned. |
Sysdate
Function | Syntax | Short Description |
---|---|---|
SYSDATE | SYSDATE | returns the current date and time set |
CASE
Function | Syntax | Short Description |
---|---|---|
CASE | CASE { expr WHEN comparison_expr THEN return_expr [ WHEN comparison_expr THEN return_expr ]… | WHEN condition THEN return_expr [ WHEN condition THEN return_expr ]… } [ ELSE else_expr ] END | returns value based on different conditions |
Aggregate Functions
Function | Syntax | Short Description |
---|---|---|
DENSE_RANK |
|
Computes the rank of a value with respect to other values and returns the rank as a NUMBER. Always results in consecutive rankings |
KEEP() | KEEP(DENSE_RANK { FIRST | LAST } ORDER BY expr [ NULLS { FIRST | LAST } ]) | Qualifies an aggregate function indicating that only the FIRST or LAST values of the function are returned |
RANK |
|
Computes the rank of a value in a group of values. Can result in non-consecutive rankings if values are the same. |