SQL Expressions

The SQL expressions that you enter in field formulas call the Oracle database to evaluate the function, and those functions are maintained by Oracle. For more information about these functions, see the Oracle Database SQL Reference.

Important:

The following material is provided for convenience only. It is assumed that you are familiar with the implementation of SQL expressions. For a complete reference to SQL expressions, go to the Oracle website (requires Oracle account activation). Not all of the expressions described at the URL are supported in NetSuite.

If you are familiar with Microsoft SQL Server functions but are new to Oracle databases, see Character Functions to compare SQL functions support in Microsoft SQL Server and Oracle databases. If you are used to Microsoft Excel functions, please note that not all Excel functions are supported by Oracle, and those that are supported often use a different syntax.

The following tables outline the SQL functions that can be used in NetSuite 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
Analytic and Aggregate Functions

Important:

To avoid cross-scripting (XSS) vulnerabilities, search column results with formulas that contain <script> tags do not display script output. Do not use <script> tags in your search formulas.

Note:

You cannot combine aggregate and non-aggregate SQL functions in the same formula definition. For example, if you create a formula using an aggregate function, then all the functions in the definition must either be aggregate functions or listed in a GROUP_BY clause.

Conventions in SQL Syntax Examples

The syntax examples use Oracle's typographic conventions for SQL code examples. The following table shows the conventions used in SQL syntax examples.

Convention	Meaning	Example
[ ]	Anything enclosed in brackets is optional.	LTRIM(char [ , set ])
{ }	Braces are used for grouping items.	TO_CHAR({ datetime \| interval } [, fmt [, 'nlsparam' ] ])
\|	A vertical bar represents a choice of two options.	ATAN2(n1 { , \| / } n2)
...	An ellipsis mean repetition in syntax descriptions or an omission in code examples or text.	DECODE(expr, search, result [, search, result ]...[, default ])
UPPERCASE	Indicates an element supplied by the system. These terms are shown in uppercase to distinguish them from terms you define. Unless terms appear in brackets, enter them in the order and with the spelling shown. Because these terms are not case sensitive, you can use them in either UPPERCASE or lowercase.	TRIM([ { { LEADING \| TRAILING \| BOTH }[ trim_character ] \| trim_character } FROM ] trim_source)
lowercase	Indicates user-defined programmatic elements, such as field names. Note that some programmatic elements use a mixture of UPPERCASE and lowercase. Enter these elements as shown.	NULLIF(expr1, expr2)

Convention

Meaning

Example

[ ]

Anything enclosed in brackets is optional.

LTRIM(char [ , set ])

{ }

Braces are used for grouping items.

TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])

A vertical bar represents a choice of two options.

ATAN2(n1 { , | / } n2)

...

An ellipsis mean repetition in syntax descriptions or an omission in code examples or text.

DECODE(expr, search, result [, search, result ]...[, default ])

UPPERCASE

Indicates an element supplied by the system. These terms are shown in uppercase to distinguish them from terms you define. Unless terms appear in brackets, enter them in the order and with the spelling shown. Because these terms are not case sensitive, you can use them in either UPPERCASE or lowercase.

TRIM([ { { LEADING | TRAILING | BOTH }[ trim_character ] | trim_character } FROM ] trim_source)

lowercase

Indicates user-defined programmatic elements, such as field names.

Note that some programmatic elements use a mixture of UPPERCASE and lowercase. Enter these elements as shown.

NULLIF(expr1, expr2)

The examples are provided for illustrative purposes only. They may or may not work for you, depending on your NetSuite account settings and data.

Numeric Functions

Function	Syntax	Description	Example
ABS	ABS(n)	returns the absolute value of n	ABS({amount})
ACOS	ACOS(n)	returns the arc cosine of n	ACOS(0.35)
ASIN	ASIN(n)	returns the arc sine of n	ASIN(1)
ATAN	ATAN(n)	returns the arc tangent of n	ATAN(0.2)
ATAN2	ATAN2(n1 { , \| / } n2)	returns the arc tangent of n1 and n2	ATAN2(0.2, 0.3)
BITAND	BITAND(expr1, expr2)	computes an AND operation on the bits of expr1 and expr2	BITAND(5, 3)
CEIL	CEIL(n)	returns smallest integer greater than or equal to n	CEIL({today}-{createddate})
COS	COS(n)	returns the cosine of n	COS(0.35)
COSH	COSH(n)	returns the hyperbolic cosine of n	COSH(-3.15)
EXP	EXP(n)	returns e raised to the nth power, where e =2.71828183	EXP({rate})
FLOOR	FLOOR(n)	returns largest integer equal to or less than n	FLOOR({today}-{createddate})
LN	LN(n)	returns the natural logarithm of n	LN(20)
LOG	LOG(n2, n1)	returns the logarithm, base n2, of n1	LOG(10, 20)
MOD	MOD(n2, n1)	returns the remainder of n2 divided by n1	MOD({today}-{lastmessagedate},7)
NANVL	NANVL(n2, n1)	returns an alternative value n1 if the input value n2 is not a number	NANVL({itemisbn13}, '')
POWER	POWER(n2, n1)	returns n2 raised to the n1 power	POWER({custcoldaystoship},-.196)
REMAINDER	REMAINDER(n2, n1)	returns the remainder of n2 divided by n1	REMAINDER({transaction.totalamount}, {transaction.amountpaid})
ROUND (number)	ROUND(n [, integer ])	returns n rounded to integer places to the right of the decimal point	ROUND(({today}-{startdate}), 0)
SIGN	SIGN(n)	returns the sign of n	SIGN({quantity})
SIN	SIN(n)	returns the sine of n	SIN(5.2)
SINH	SINH(n)	returns the hyperbolic sine of n	SINH(3)
SQRT	SQRT(n)	returns the square root of n	SQRT(POWER({taxamount}, 2))
TAN	TAN(n)	returns the tangent of n	TAN(-5.2)
TANH	TANH(n)	returns the hyperbolic tangent of n	TANH(3)
TRUNC (number)	TRUNC(n1 [, n2 ])	returns n1 truncated to n2 decimal places	TRUNC({amount}, 1)

Character Functions Returning Character Values

Function	Syntax	Description	Example
CHR	CHR(n [ USING NCHAR_CS ])	returns the character having the binary equivalent to n as a VARCHAR2 value	CHR(13)
CONCAT	CONCAT(char1, char2)	concatenates char1 and char2 into one string	CONCAT({number},CONCAT('_',{line}))
INITCAP	INITCAP(char)	returns char, with the first letter of each word in uppercase, all other letters in lowercase	INITCAP({customer.companyname})
LOWER	LOWER(char)	returns char, with all letters lowercase	LOWER({customer.companyname})
LPAD	LPAD(expr1, n [, expr2 ])	returns expr1, left-padded to length n characters with the sequence of characters in expr2	LPAD({line},3,'0')
LTRIM	LTRIM(char [, set ])	removes from the left end of char all of the characters contained in set	LTRIM({companyname},'-')
REGEXP_REPLACE	REGEXP_REPLACE(source_char, pattern [, replace_string [, position [, occurrence [, match_parameter ] ] ] ] )	lets you replace a sequence of characters (source_char) that matches a regular expression pattern with another set of characters (replace_string)	REGEXP_REPLACE({name}, '^.*:', '')
REGEXP_SUBSTR	REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_parameter ] ] ] )	lets you extract a sequence of characters that matches a regular expression pattern from the source string (source_char)	REGEXP_SUBSTR({item},'[^:]+$')
REPLACE	REPLACE(char, search_string [, replacement_string ] )	returns char with every occurrence of search_string replaced with replacement_string	REPLACE({serialnumber}, '&', ',')
RPAD	RPAD(expr1 , n [, expr2 ])	returns expr1, right-padded to length n characters with expr2, replicated as many times as necessary	RPAD({firstname},20)
RTRIM	RTRIM(char [, set ])	removes from the right end of char all of the characters that appear in set	RTRIM({paidtransaction.externalid},'-Invoice')
SOUNDEX	SOUNDEX(char)	returns a character string containing the phonetic representation of char	SOUNDEX({companyname})
SUBSTR	SUBSTR(char, position [, substring_length ])	returns a portion of char, beginning at character position, substring_length characters long	SUBSTR({transaction.salesrep},1,3)
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	TRANSLATE({expensecategory}, ' ', '+')
TRIM	TRIM([ { { LEADING \| TRAILING \| BOTH } [ trim_character ] \| trim_character } FROM ] trim_source )	lets you trim leading or trailing characters (or both) from a character string	TRIM (BOTH ',' FROM {custrecord_assetcost})
UPPER	UPPER(char)	returns char, with all letters uppercase	UPPER({unit})

Character Functions Returning Number Values

Function	Syntax	Short Description	Example
ASCII	ASCII(char)	returns the decimal representation in the database character set of the first character of char	ASCII({taxitem})
INSTR	INSTR(string , substring [, position [, occurrence ] ])	searches string for substring	INSTR({messages.message}, 'cspdr3')
LENGTH	LENGTH(char)	returns the length of char	LENGTH({name})
REGEXP_INSTR	REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_option [, match_parameter ] ] ] ] )	lets you search a string for a regular expression pattern	REGEXP_INSTR ({item.unitstype}, '\d')
TO_NUMBER()	TO_NUMBER(expr [, fmt [, 'nlsparam' ] ])	converts a formatted TEXT or NTEXT expression to a number	TO_NUMBER({quantity})

Datetime Functions

Function	Syntax	Short Description	Example
ADD_MONTHS	ADD_MONTHS(date, integer)	returns the date plus integer months	ADD_MONTHS({today},-1)
LAST_DAY	LAST_DAY(date)	returns the date of the last day of the month that contains date	LAST_DAY({today})
MONTHS_BETWEEN	MONTHS_BETWEEN(date1, date2)	returns the number of months between date1 and date2	MONTHS_BETWEEN(SYSDATE,{createddate}) See also Sysdate.
NEXT_DAY	NEXT_DAY(date, char)	returns the date of the first weekday named by char that is later than the date	NEXT_DAY({today},'SATURDAY')
ROUND (DATE)	ROUND(date [, fmt ])	returns date rounded to the unit specified by the format model fmt	ROUND(TO_DATE('12/31/2014', 'mm/dd/yyyy')-{datecreated})
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_CHAR({date}, 'hh24')
TO_DATE()	TO_DATE(char [, fmt [, 'nlsparam' ] ])	converts a formatted TEXT or NTEXT expression to a DATETIME value	TO_DATE('31.12.2011', 'DD.MM.YYYY')
TRUNC (DATE)	TRUNC(date [, fmt ])	returns date with the time portion of the day truncated to the unit specified by the format model fmt	TRUNC({today},'YYYY')

NULL-Related Functions

Function	Syntax	Short Description	Example
COALESCE	COALESCE(expr [, expr ]...)	returns the first non-null expr in the expression list.	COALESCE({quantitycommitted}, 0)
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.	NULLIF({price}, 0)
NVL	NVL(expr1, expr2)	lets you replace null with the second parameter.	NVL({quantity},'0')
NVL2	NVL2(expr1, expr2, expr3)	If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.	NVL2({location}, 1, 2)

Decode

Function	Syntax	Short Descriptions	Example
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.	DECODE({systemnotes.name}, {assigned},'T','F')

Sysdate

Function	Syntax	Short Description	Example
SYSDATE	SYSDATE	returns the current date and time set	TO_DATE(SYSDATE, 'DD.MM.YYYY') or TO_CHAR(SYSDATE, 'mm/dd/yyyy') See also TO_DATE and TO_CHAR in the Datetime Functions.

Function

Syntax

Short Description

Example

SYSDATE

returns the current date and time set

TO_DATE(SYSDATE, 'DD.MM.YYYY')

TO_CHAR(SYSDATE, 'mm/dd/yyyy')

See also TO_DATE and TO_CHAR in the Datetime Functions.

Case

Function	Syntax	Short Description	Example
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	CASE {state} WHEN 'NY' THEN 'New York' WHEN 'CA' THEN 'California' ELSE {state} END or CASE WHEN {quantityavailable} > 19 THEN 'In Stock' WHEN {quantityavailable} > 1 THEN 'Limited Availability' WHEN {quantityavailable} =1 THEN 'The Last Piece' WHEN {quantityavailable} IS NULL THEN 'Discontinued' ELSE 'Out of Stock' END

Function

Syntax

Short Description

Example

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

CASE {state}

WHEN 'NY' THEN 'New York'

WHEN 'CA' THEN 'California'

ELSE {state}

END

CASE

WHEN {quantityavailable} > 19 THEN 'In Stock'

WHEN {quantityavailable} > 1 THEN 'Limited Availability'

WHEN {quantityavailable} =1 THEN 'The Last Piece'

WHEN {quantityavailable} IS NULL THEN 'Discontinued'

ELSE 'Out of Stock'

END

Analytic and Aggregate Functions

Function	Syntax	Short Description	Example
DENSE_RANK	DENSE_RANK(expr [, expr ]...) WITHIN GROUP (ORDER BY expr [ DESC \| ASC ] [ NULLS { FIRST \| LAST } ] [,expr [ DESC \| ASC ] [ NULLS { FIRST \| LAST } ] ]... ) or DENSE_RANK() OVER (PARTITION by expr [,expr...] 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.	DENSE_RANK ({amount}) WITHIN GROUP (ORDER BY {AMOUNT}) or DENSE_RANK() OVER (PARTITION BY {name} ORDER BY {trandate} DESC)
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.	KEEP(DENSE_RANK LAST ORDER BY {internalid})
RANK	RANK(expr [, expr ]...) WITHIN GROUP (ORDER BY expr [ DESC \| ASC ] [ NULLS { FIRST \| LAST } ] [, expr [ DESC \| ASC ] [ NULLS { FIRST \| LAST } ] ]... ) or RANK() OVER (PARTITION by expr [,expr...] 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.	RANK() OVER (PARTITION by {tranid} ORDER BY {line} DESC) or RANK ({amount}) WITHIN GROUP (ORDER BY {amount})

Function

Syntax

Short Description

Example

DENSE_RANK

DENSE_RANK(expr [, expr ]...) WITHIN GROUP

(ORDER BY expr [ DESC | ASC ]

[ NULLS { FIRST | LAST } ]

[,expr [ DESC | ASC ]

[ NULLS { FIRST | LAST } ]

]...

)

DENSE_RANK() OVER

(PARTITION by expr

[,expr...] 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.

DENSE_RANK ({amount}) WITHIN GROUP (ORDER BY {AMOUNT})

DENSE_RANK() OVER (PARTITION BY {name} ORDER BY {trandate} DESC)

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.

KEEP(DENSE_RANK LAST ORDER BY {internalid})

RANK

RANK(expr [, expr ]...) WITHIN GROUP

(ORDER BY expr [ DESC | ASC ]

[ NULLS { FIRST | LAST } ]

[, expr [ DESC | ASC ]

[ NULLS { FIRST | LAST } ]

]...

)

RANK() OVER

(PARTITION by expr

[,expr...] 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.

RANK() OVER (PARTITION by {tranid} ORDER BY {line} DESC)

RANK ({amount}) WITHIN GROUP (ORDER BY {amount})

Conventions in SQL Syntax Examples

Numeric Functions

Character Functions Returning Character Values

Character Functions Returning Number Values

Datetime Functions

NULL-Related Functions

Decode

Sysdate

Case

Analytic and Aggregate Functions

Related Topics