SQL Expressions – JCurve Solutions

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	REGEXP_REPLACE(source_char, pattern [, replace_string [, position [, occurrence [, match_parameter ] ] ] ] )	allows you to search a string for a regular expression pattern
REGEXP_SUBSTR	REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_parameter ] ] ] )	allows you to search a string for a regular expression pattern
REPLACE	REPLACE(char, search_string [, replacement_string ] )	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	{ SUBSTR \| SUBSTRB \| SUBSTRC \| SUBSTR2 \| SUBSTR4 } (char, position [, substring_length ])	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	TRIM([ { { LEADING \| TRAILING \| BOTH } [ trim_character ] \| trim_character } FROM ] trim_source )	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	{ INSTR \| INSTRB \| INSTRC \| INSTR2 \| INSTR4 } (string , substring [, position [, occurrence ] ])	searches string for substring
LENGTH	{ LENGTH \| LENGTHB \| LENGTHC \| LENGTH2 \| LENGTH4 } (char)	returns the length of char
REGEXP_INSTR	REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_option [, match_parameter ] ] ] ] )	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	DECODE(expr, search, result [, search, result ]… [, default ] )	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	DENSE_RANK(expr [, expr ]…) WITHIN GROUP (ORDER BY expr [ DESC \| ASC ] [ NULLS { FIRST \| LAST } ] [,expr [ DESC \| ASC ] [ NULLS { FIRST \| LAST } ] ]… )	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	RANK(expr [, expr ]…) WITHIN GROUP (ORDER BY expr [ DESC \| ASC ] [ NULLS { FIRST \| LAST } ] [, expr [ DESC \| ASC ] [ NULLS { FIRST \| LAST } ] ]… )	Computes the rank of a value in a group of values. Can result in non-consecutive rankings if values are the same.