## 3 Row Functions

The OLAP row functions extend the syntax of the SQL row functions for use with dimensional objects. If you use the SQL row functions, then this syntax is familiar. You can use these functions on relational data when loading it into cubes and cube dimensions, and with the OLAP functions when creating calculated measures.

This chapter describes the row functions of the OLAP expression syntax. It contains these topics:

A

B

C

D

E

F

G

H

I

L

M

N

O

P

R

S

T

U

V

W

### Row Functions By Category

The row functions are grouped into the following categories:

#### Numeric Functions

These functions accept numeric input and return numeric values:

#### Character Functions That Return Characters

These functions accept character input and return character values:

#### NLS Character Functions

These functions return information about a character set:

#### Character Functions That Return Numbers

These functions accept character input and return numeric values:

#### Datetime Functions

These functions operate on date, timestamp, or interval values:

#### General Comparison Functions

These functions determine the greatest or least value in a set of values:

#### Conversion Functions

These functions change a value from one data type to another:

#### Encoding and Decoding Function

These functions return a numeric value for each input value:

#### Null-Related Functions

These functions facilitate null handling:

#### Environment and Identifier Functions

These functions provide information about the instance and the session:

### ABS

`ABS` returns the absolute value of a numeric expression.

Return Value

`NUMBER`

Syntax

`ABS(n)`

Arguments

`n` is any numeric expression.

Example

`ABS(-15)` returns the value `15`.

### ACOS

`ACOS` calculates the angle value in radians of a specified cosine.

Return Value

`NUMBER`

Syntax

`ACOS(n) `

Arguments

`n` is a numeric expression for the cosine in the range of -1 to 1.

Example

`ACOS(.3)` returns the value `1.26610367`.

`ADD_MONTHS` returns a date that is a specified number of months after a specified date.

When the starting date is the last day of the month or when the returned month has fewer days, then `ADD_MONTHS` returns the last day of the month. Otherwise, the returned day is the starting day.

Return Value

`DATE`

Syntax

`ADD_MONTHS(date, integer) `

Arguments

`date` is the starting date.

`integer` is the number of months to be added to the starting date.

Example

`ADD_MONTHS('17-JUN-06', 1)` returns the value `17-JUL-06`.

### ASCII

`ASCII` returns the decimal representation of the first character of an expression.

Return Value

`NUMBER`

Syntax

`ASCII(char) `

Arguments

`char` can be any text expression.

Example

`ASCII('Boston')` returns the value `66`, which is the ASCII equivalent of the letter B.

### ASCIISTR

`ASCIISTR` converts a string in any character set to ASCII in the database character set. Non-ASCII characters are represented as `\`xxxx, where xxxx is a UTF-16 code unit.

Return Value

`VARCHAR2`

Syntax

`ASCIISTR(char)`

Arguments

`char` can be any character string.

Example

`ASCIISTR('Skåne')` returns the value `Sk\00E5ne`.

### ASIN

`ASIN` calculates the angle value in radians of a specified sine.

Return Value

`NUMBER`

Syntax

`ASIN(n) `

Arguments

`n` is a numeric expression in the range of -1 to 1 that contains the decimal value of a sine.

Example

`ASIN(.3)` returns the value `0.304692654`.

### ATAN

`ATAN` calculates the angle value in radians of a specified tangent.

Use `ATAN2` to retrieve a full-range (0 - 2 pi) numeric value indicating the arc tangent of a given ratio.

Return Value

`NUMBER`

Syntax

`ATAN(n) `

Arguments

`n` is a numeric expression that contains the decimal value of a tangent.

Example

`ATAN(.3)` returns the value `0.291456794`.

### ATAN2

`ATAN2` returns a full-range (0 - 2 pi) numeric value of the arc tangent of a given ratio. The function returns values in the range of -pi to pi, depending on the signs of the arguments.

Use `ATAN` to calculate the angle value (in radians) of a specified tangent that is not a ratio.

Return Value

`NUMBER`

Syntax

`ATAN2(n1, n2) `

Arguments

`n1` and `n2` are numeric expressions for the components of the ratio.

Example

`ATAN2(.3, .2)` returns the value `0.982793723`.

### BIN_TO_NUM

`BIN_TO_NUM` converts a bit vector to its equivalent number.

Return Value

`NUMBER`

Syntax

`BIN_TO_NUM(expr [, expr ]... ) `

Arguments

`expr` is a numeric expression with a value of 0 or 1 for the value of a bit in the bit vector.

Example

`BIN_TO_NUM(1,0,1,0)` returns the value `10`.

### BITAND

`BITAND` computes an `AND` operation on the bits of two nonnegative integers, and returns an integer. This function is commonly used with the `DECODE` function.

An `AND` operation compares two bit values. If both values are 1, the operator returns 1. If one or both values are 0, the operator returns 0.

Return Value

`NUMBER`

Syntax

`BITAND(expr1, expr2) `

Arguments

`expr1` and `expr2` are numeric expressions for nonnegative integers.

Example

`BITAND(7, 29)` returns the value `5`.

The binary value of `7` is `111` and of `29` is `11101`. A bit-by-bit comparison generates the binary value `101`, which is decimal `5`.

### CAST

`CAST` converts values from one data type to another.

Return Value

The data type specified by `type_name`.

Syntax

`CAST(expr AS type_name)`

Arguments

`expr` can be an expression in one of the data types.

`type_name` is one of the data types listed in Table 1-2.

Table 3-1 shows which data types can be cast into which other built-in data types. `NUMBER` includes `NUMBER`, `DECIMAL`, and `INTEGER`. `DATETIME` includes `DATE`, `TIMESTAMP`, `TIMESTAMP WITH TIMEZONE`, and `TIMESTAMP WITH LOCAL TIMEZONE`. `INTERVAL` includes `INTERVAL DAY TO SECOND` and `INTERVAL YEAR TO MONTH`.

Table 3-1 Compatible Data Types

From To BINARY_FLOAT, BINARY_DOUBLE To CHAR, VARCHAR2 To NUMBER To DATETIME, INTERVAL To NCHAR, NVARCHAR2

BINARY_FLOAT, BINARY_DOUBLE

yes

yes

yes

no

yes

CHAR, VARCHAR2

yes

yes

yes

yes

no

NUMBER

yes

yes

yes

no

yes

DATETIME, INTERVAL

no

yes

no

yes

yes

NCHAR, NVARCHAR2

yes

no

yes

no

yes

Example

`CAST('123.4567' AS NUMBER(10,2))` returns the value `123.46`.

### CEIL

`CEIL` returns the smallest whole number greater than or equal to a specified number.

Return Value

`NUMBER`

Syntax

`CEIL(n) `

Arguments

`n` is a numeric expression.

Examples

`CEIL(3.1415927)` returns the value `4`.

`CEIL(-3.4)` returns the value `-3.00`.

### CHARTOROWID

`CHARTOROWID` converts a value from a text data type to a `ROWID` data type.

For more information about the `ROWID` pseudocolumn, refer to the Oracle Database SQL Language Reference.

Return Value

`ROWID`

Syntax

`CHARTOROWID(char)`

Arguments

`char` is a text expression that forms a valid rowid.

Example

`chartorowid('AAAN6EAALAAAAAMAAB')` returns the text string `AAAN6EAALAAAAAMAAB` as a rowid.

### CHR

`CHR` converts an integer to the character with its binary equivalent in either the database character set or the national character set.

For single-byte character sets, if `n` > 256, then `CHR` converts the binary equivalent of `mod(n, 256)`.

For the Unicode national character sets and all multibyte character sets, `n` must resolve to one entire code point. Code points are not validated, and the result of specifying invalid code points is indeterminate.

Return Value

`VARCHAR2 | NVARCHAR2`

Syntax

`CHR(n [ USING NCHAR_CS ]) `

Arguments

`n` is a numeric expression.

`USING NCHAR_CS` returns a character in the national character set. Otherwise, the return value is in the database character set. The OLAP engine uses the UTF8 national character set, so the return value may be different from the SQL `CHR` function, which uses the database UTF16 national character set.

Example

`CHR(67)`, `CHR(67 USING NCHAR_CS)`, and `CHR(323)` all return the letter `C` on an ASCII-based system with the WE8DEC database character set and the UTF8 national character set. `CHR(323)` is evaluated as `CHR(MOD(323, 256))`.

### COALESCE

`COALESCE` returns the first non-null expression in a list of expressions, or `NULL` when all of the expressions evaluate to null.

Return Value

Data type of the first argument

Syntax

`COALESCE(expr [, expr ]...) `

Arguments

`expr` can be any expression.

Examples

`COALESCE(5, 8, 3)` returns the value `5`.

`COALESCE(NULL, 8, 3)` returns the value `8`.

### CONCAT

`CONCAT` joins two expressions as a single character string. The data type of the return value is the same as the expressions, or if they are mixed, the one that results in a lossless conversion.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`CONCAT(char1, char2)`

Arguments

`char1` and `char2` are text expressions.

Example

`CONCAT('The current date is ', 'October 13, 2006')` returns the string `The current date is October 13, 2006`.

### COS

`COS` calculates the cosine of an angle.

Return Value

`NUMBER`

Syntax

`COS(n)`

Arguments

`n` is a numeric expression for an angle represented in radians.

Example

`COS(180 * 3.1415927/180)` returns the cosine of 180 degrees as the value `-1`. The expression converts degrees to radians.

### COSH

`COSH` calculates the cosine of a hyperbolic angle.

Return Value

`NUMBER`

Syntax

`COSH(n)`

Arguments

`n` is a numeric expression for a hyperbolic angle.

Example

`COSH(0)` returns the value `1`.

### CURRENT_DATE

`CURRENT_DATE` returns the current date in the session time zone.

Return Value

`DATE`

Syntax

`CURRENT_DATE`

Example

`CURRENT_DATE` returns a value such as `12-AUG-08`.

### CURRENT_TIMESTAMP

`CURRENT_TIMESTAMP` returns the current date and time in the session time zone. The time zone offset identifies the current local time of the session.

Return Value

`TIMESTAMP WITH TIME ZONE`

Syntax

`CURRENT_TIMESTAMP [ (precision) ]`

Arguments

`precision` specifies the fractional second precision of the returned time value. The default value is 6.

Examples

`CURRENT_TIMESTAMP` returns a value such as `08-AUG-06 11.18.31.082257 AM -08:00`.

`CURRENT_TIMESTAMP(2)` returns a value such as `08-AUG-06 11.18.31.08 AM -08:00`.

### DBTIMEZONE

`DBTIMEZONE` returns the value of the database time zone as either a time zone offset from Coordinated Universal Time (UTC) or a time zone region name.

To obtain other time zone offsets, use `TZ_OFFSET`.

Return Value

`VARCHAR2`

Syntax

`DBTIMEZONE`

Example

`DBTIMEZONE` returns the offset `-08:00` for Mountain Standard Time.

### DECODE

`DECODE` compares an expression to one or more search strings one by one.

If `expr` is `search`, then `DECODE` returns the corresponding `result`. If there is no match, then `DECODE` returns `default`. If you omit `default`, then `DECODE` returns `NULL`.

Return Value

Data type of the first `result` argument

Syntax

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

Arguments

`expr` is an expression that is compared to one or more search strings.

`search` is a string that is searched for a match to `expr`.

`result` is the return value when `expr` matches the corresponding search string.

`default` is the return value when `expr` does not match any of the search strings. If `default` is omitted, then `DECODE` returns `NULL`.

The arguments can be any numeric or character type. Two nulls are equivalent. If `expr` is null, then `DECODE` returns the `result` of the first `search` that is also null.

The maximum number of components, including `expr`, `searches`, `results`, and `default`, is 255.

Example

`DECODE(sysdate, '21-JUN-06', 'Summer Solstice', '21-DEC-06', 'Winter Solstice', 'Have a nice day!')`

returns these values:

`Summer Solstice` on June 21, 2006

`Winter Solstice` on December 21, 2006

`Have a nice day!` on all other days

### EXP

`EXP` returns `e` raised to the `n`th power, where `e` = 2.71828183. The function returns a value of the same type as the argument.

Return Value

`NUMBER`

Syntax

`EXP(n)`

Arguments

`n` is a numeric expression for the exponent.

Example

`EXP(4)` returns the value `54.59815`.

### EXTRACT (datetime)

`EXTRACT` returns the value of a specified field from a datetime or interval expression.

Return Value

`NUMBER`

Syntax

```EXTRACT( { { YEAR
| MONTH
| DAY
| HOUR
| MINUTE
| SECOND
}
| { TIMEZONE_HOUR
| TIMEZONE_MINUTE
}
| { TIMEZONE_REGION
| TIMEZONE_ABBR
}
}
FROM   { datetime_value_expression
| interval_value_expression
}
)```

Arguments

`datetime_value_expression` is an expression with a datetime data type.

`interval_value_expression` is an expression with an interval data type.

Example

`EXTRACT(MONTH FROM CURRENT_TIMESTAMP)` returns the value `8` for August when the current timestamp is `08-AUG-06 01.10.55.330120 PM -07:00`.

`EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP)` returns the value `-7` from the same example.

### FLOOR

`FLOOR` returns the largest integer equal to or less than a specified number.

Return Value

`NUMBER `

Syntax

`FLOOR(n)`

Arguments

`n` can be any numeric expression.

Examples

`FLOOR(15.7)` returns the value `15`.

`FLOOR(-15.7)` returns the value `-16`.

### FROM_TZ

`FROM_TZ` converts a timestamp value and a time zone to a `TIMESTAMP WITH TIME ZONE` data type.

Return Value

`TIMESTAMP WITH TIME ZONE`

Syntax

`FROM_TZ (timestamp_value, time_zone_value)`

Arguments

`timestamp_value` is an expression with a `TIMESTAMP` data type.

`time_zone_value` is a text expression that returns a string in the format `TZH:TZM` or in `TZR` with optional `TZD` format.

Example

`FROM_TZ(TIMESTAMP '2008-03-26 08:00:00', '3:00')` returns the value `26-MAR-08 08.00.00.000000 AM +03:00`.

### GREATEST

`GREATEST` returns the largest expression in a list of expressions. All expressions after the first are implicitly converted to the data type of the first expression before the comparison. Text expressions are compared character by character.

To retrieve the smallest expression in a list of expressions, use `LEAST`.

Return Value

The data type of the first expression

Syntax

`GREATEST(expr [, expr ]...)`

Arguments

`expr` can be any expression.

Examples

`GREATEST('Harry','Harriot','Harold')` returns the value `Harry`.

`GREATEST(7, 19, 3)` returns the value `19`.

### HEXTORAW

`HEXTORAW` converts a hexadecimal value to a raw value.

Return Value

`RAW`

Syntax

`HEXTORAW (char)`

Arguments

`char` is a hexadecimal value in the `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type.

Example

`HEXTORAW('7D')` returns the `RAW` value `7D`.

### INITCAP

`INITCAP` returns a specified text expression, with the first letter of each word in uppercase and all other letters in lowercase. Words are delimited by white space or non-alphanumeric characters. The data type of the return value is the same as the original text.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`INITCAP(char)`

Arguments

`char` can be any text expression.

Example

`INITCAP('top ten tunes')` and `INITCAP('TOP TEN TUNES')` both return the string `Top Ten Tunes`.

### INSTR

The `INSTR` functions search `string` for `substring`. The function returns an integer indicating the position of the character in `string`, or a zero (0) if does not find a match.

• `INSTR` calculates strings using characters as defined by the input character set.

• `INSTRB` uses bytes instead of characters.

• `INSTRC` uses Unicode complete characters.

`REGEXP_INSTR` provides additional options.

Return Value

`NUMBER`

Syntax

```{ INSTR | INSTRB | INSTRC }
(string , substring [, position [, occurrence ] ])```

Arguments

`string` is the text expression to search.

`substring` is the text string to search for.

`position` is a nonzero integer indicating the character in `string` where the function begins the search. When position is negative, then `INSTR` counts and searches backward from the end of string. The default value of position is 1, which means that the function begins searching at the first character of string.

`occurrence` is an integer indicating which occurrence of string the function should search for. The value of occurrence must be positive. The default values of occurrence is 1, meaning the function searches for the first occurrence of `substring`.

Example

`INSTR('CORPORATE FLOOR','OR', 3, 2)` searches the string CORPORATE FLOOR beginning with the third character (R) for the second instance of the substring OR. It returns the value `14`, which is the position of the second O in FLOOR.

### LAST_DAY

`LAST_DAY` returns the last day of the month in which a particular date falls.

Return Value

`DATE`

Syntax

`LAST_DAY(date)`

Arguments

`date` can be any datetime expression.

Example

`LAST_DAY('26-MAR-06')` returns the value `31-MAR-06`.

### LEAST

`LEAST` returns the smallest expression in a list of expressions. All expressions after the first are implicitly converted to the data type of the first expression before the comparison. Text expressions are compared character by character.

To retrieve the largest expression in a list of expressions, use `GREATEST`.

Return Value

The data type of the first expression

Syntax

`LEAST(expr [, expr ]...)`

Arguments

`expr` can be any expression.

Examples

`LEAST('Harry','Harriot','Harold')` returns the value `Harold`.

`LEAST(19, 3, 7)` returns the value `3`.

### LENGTH

The `LENGTH` functions return the length of a text expression.

• `LENGTH` counts the number of characters.

• `LENGTHB` uses bytes instead of characters.

• `LENGTHC` uses Unicode complete characters.

Return Value

`NUMBER`

Syntax

`{ LENGTH | LENGTHB | LENGTHC }(char)`

Arguments

`char` is any text expression.

Example

`LENGTH('CANDIDE')` returns the value `7`.

### LN

`LN` returns the natural logarithm of a number greater than 0.

Return Value

`NUMBER`

Syntax

`LN(n)`

Arguments

`n` can be any numeric expression with a value greater than 0.

Example

`LN(95)` returns the value `4.55387689`.

### LNNVL

`LNNVL` evaluates a condition when one or both operands of the condition may be null. `LNNVL` can be used anywhere a scalar expression can appear, even in contexts where the `IS [NOT] NULL`, `AND`, or `OR` conditions are not valid but would otherwise be required to account for potential nulls.

NOTE: This function returns `1` (true) if the condition is false or unknown, and `0` (false) if the condition is true.

Return Value

`NUMBER`

Syntax

`LNNVL(condition)`

Arguments

`condition` can be any expression containing scalar values.

Examples

`LNNVL(1 > 4)` returns `1` (true).

### LOCALTIMESTAMP

`LOCALTIMESTAMP` returns the current date and time in the session time zone.

Return Value

`TIMESTAMP`

Syntax

`LOCALTIMESTAMP [ (timestamp_precision) ]`

Arguments

`timestamp_precision` specifies the fractional second precision of the time value returned.

Examples

`LOCALTIMESTAMP` returns a value such as `09-AUG-06 08.11.37.045186 AM`.

`LOCALTIMESTAMP(2)` returns a value such as `09-AUG-06 08.11.37.040000 AM`.

### LOG

`LOG` computes the logarithm of an expression.

Return Value

`NUMBER`

Syntax

`LOG(n2, n1)`

Arguments

`n2` is the base by which to compute the logarithm.

`n1` is the value whose logarithm is calculated. It can be any numeric expression that is greater than zero. When the value is equal to or less than zero, `LOG` returns a null value.

Example

`LOG(10,100)` returns the value `2`.

### LOWER

`LOWER` converts all alphabetic characters in a text expression to lowercase. The data type of the return value is the same as the original text.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`LOWER(char)`

Arguments

`char` can be any text expression.

Example

`LOWER('STOP SHOUTING')` returns the string `stop shouting`.

`LPAD` adds characters to the left of an expression to a specified length. The data type of the return value is the same as the original text.

Use `RPAD` to add characters to the right.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`LPAD(expr1, n [, expr2 ])`

Arguments

`expr1` is a text expression for the base string.

`n` is the total length of the returned expression. If `expr1` is longer than `n`, then this function truncates `expr1` to `n` characters.

`expr2` is a text expression for the padding characters. By default, it is a space.

Example

`LPAD('Page 1',15,'*.')` returns the value `*.*.*.*.*Page 1`.

`LPAD('Stay tuned', 4)` returns the value `Stay`.

### LTRIM

`LTRIM` scans a text expression from left to right and removes all the characters that match the characters in the trim expression, until it finds an unmatched character. The data type of the return value is the same as the original text.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`LTRIM(char [, set ])`

Arguments

`char` is the text expression to be trimmed.

`set` is a text expression with the characters to remove. The default value of `set` is a single blank.

Examples

`LTRIM(' . . . .Last Word', ' .')` returns the value `Last Word`.

### MOD

`MOD` returns the remainder after a number is divided by another, or the number if the divisor is 0 (zero).

Return Value

`NUMBER`

Syntax

`MOD(n2, n1)`

Arguments

`n2` is a numeric expression for the number to be divided.

`n1` is a numeric expression for the divisor.

Example

`MOD(13,7)` returns the value `6`.

### MONTHS_BETWEEN

`MONTHS_BETWEEN` calculates the number of months between two dates. When the two dates have the same day component or are both the last day of the month, then the return value is a whole number. Otherwise, the return value includes a fraction that considers the difference in the days based on a 31-day month.

Return Value

`NUMBER`

Syntax

`MONTHS_BETWEEN(date1, date2)`

Arguments

`date1` and `date2` are datetime expressions. If `date1` is later than `date2`, then the result is positive. If `date1` is earlier than `date2`, then the result is negative.

Example

`MONTHS_BETWEEN('15-APR-06', '01-JAN-06')` returns the value `3.4516129`.

### NANVL

`NANVL` checks if a value is a number. If it is, then `NANVL` returns that value. If not, it returns an alternate value. This function is typically used to convert a binary double or binary float NaN (Not a Number) value to zero or null.

Return Value

`datatype`

Syntax

`NANVL (expression, alternate)`

Arguments

`expression` can be any value.

`alternate` is the numeric value returned if `expression` is not a number.

### NEW_TIME

`NEW_TIME` converts the date and time from one time zone to another. Before using this function, set the `NLS_DATE_FORMAT` parameter to display 24-hour time.

Return Value

`DATE`

Syntax

`NEW_TIME(date, timezone1, timezone2)`

Arguments

`date` is a datetime expression to be converted to a new time zone.

`timezone1` is the time zone of `date`.

`timezone2` is the new time zone.

The time zone arguments are limited to the values in Table 3-2. For other time zones, use `FROM_TZ`.

Table 3-2 Time Zones

Time Zone Abbreviation

HDT

HST

Atlantic Daylight Time

Atlantic Standard Time

AST

Bering Daylight Time

BDT

Bering Standard Time

BST

Central Daylight Time

CDT

Central Standard Time

CST

Eastern Daylight Time

EDT

Eastern Standard Time

EST

Greenwich Mean Time

GMT

Mountain Daylight Time

MDT

Mountain Standard Time

MST

Newfoundland Standard Time

NST

Pacific Daylight Time

PDT

Pacific Standard Time

PST

Yukon Daylight Time

YDT

Yukon Standard Time

YST

Example

`NEW_TIME(SYSDATE, 'PST', 'EST')` returns a value such as `18-JAN-07 04:38:07` in Eastern Standard Time when `SYSDATE` is `18-JAN-07 01:38:07` in Pacific Standard Time. For this example, `NLS_DATE_FORMAT` is set to `DD-MON-RR HH:MI:SS`.

### NEXT_DAY

`NEXT_DAY` returns the date of the first instance of a particular day of the week that follows the specified date.

Return Value

`DATE`

Syntax

`NEXT_DAY(date, char)`

Arguments

`date` is a datetime expression.

`char` is a text expression that identifies a day of the week (for example, Monday) in the language of your session.

Example

`NEXT_DAY('11-SEP-01', 'Monday')` returns the value `17-SEP-01`.

### NLS_CHARSET_ID

`NLS_CHARSET_ID` returns the identification number corresponding to a specified character set name.

Return Value

`NUMBER`

Syntax

`NLS_CHARSET_ID  ( charset_name )`

Arguments

`charset_name` is a VARCHAR2 expression that is a valid character set name.

Example

`NLS_CHARSET_ID('AL32UTF8')` returns the value `873`.

### NLS_CHARSET_NAME

`NLS_CHARSET_NAME` returns the name corresponding to a specified character set number.

Return Value

`VARCHAR2`

Syntax

`NLS_CHARSET_NAME (charset_id)`

Arguments

`charset_id` is a valid character set number or one of these keywords:

• `CHAR_CS` represents the database character set.

• `NCHAR_CS` represents the national character set. The national character set for the database can be either UTF-8 or AL16UTF16 (default). However, the national character set for analytic workspaces is always UTF-8.

If the number does not correspond to a character set, then the function returns `NULL`.

Example

`NLS_CHARSET_NAME(2000)` returns the value `AL16UTF16`.

### NLS_INITCAP

`NLS_INITCAP` returns a string in which each word begins with a capital followed by lower-case letters. White space and nonalphanumeric characters delimit the words.

Return Value

`VARCHAR2`

Syntax

`NLS_INITCAP (char [, 'nlsparam' ])`

Arguments

`char` can be any text string.

`nlsparam` can have the form `'NLS_SORT =``sort``'` where `sort` is either a linguistic sort sequence or `BINARY`. The linguistic sort sequence handles special linguistic requirements for case conversions. These requirements can result in a return value of a different length than `char`. If you omit `nlsparam`, then this function uses the default sort sequence for your session.

Example

`NLS_INITCAP('WALKING&THROUGH*A*winter wonderland')` returns the value `Walking#Through*A*Winter Wonderland`.

`NLS_INITCAP('ijsland')` returns the value `Ijsland`, but `NLS_INITCAP(NLS_INITCAP('ijsland', 'NLS_SORT = XDutch')` returns `IJsland`.

### NLS_LOWER

`NLS_LOWER` converts all alphabetic characters in a text expression to lowercase. The data type of the return value is the same as the original text.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`NLS_LOWER (char [, 'nlsparam' ])`

Arguments

`char` can be any text expression.

`nlsparam` is a linguistic sort sequence in the form `NLS_SORT =``sort``[_ai |_ci]`, where `sort` is an NLS language. You can add a suffix to the language to modify the sort: `_ai` for an accent-insensitive sort, or `_ci` for a case-insensitive sort.

Example

`NLS_LOWER('STOP SHOUTING')` returns the string `stop shouting`.

### NLS_UPPER

`NLS_UPPER` converts all alphabetic characters in a text expression to uppercase. The data type of the return value is the same as the original text.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`NLS_UPPER (char [, 'nlsparam' ])`

Arguments

`char` can be any text expression.

`nlsparam` is a linguistic sort sequence in the form `NLS_SORT =``sort``[_ai |_ci]`, where `sort` is an NLS language. You can add a suffix to the language to modify the sort: `_ai` for an accent-insensitive sort, or `_ci` for a case-insensitive sort.

Example

`NLS_UPPER('This is an emergency')` returns the string `THIS IS AN EMERGENCY`.

### NLSSORT

`NLSSORT` returns the string of bytes used to sort a text string. You can use this function to specify sorting and comparison operations based on a linguistic sort sequence rather than on the binary value of a string.

Note: `NLSSORT` returns a `RAW` value, which you may pass to another function. To create a measure or a calculated measure for the values returned by `NLSSORT`, use the `RAWTOHEX` function.

Return Value

`RAW`

Syntax

`NLSSORT(char [, 'nlsparam' ])`

Arguments

`char` is a text expression.

`nlsparam` is a linguistic sort sequence in the form `NLS_SORT =``sort``[_ai |_ci]`, where `sort` is an NLS language. You can add a suffix to the language to modify the sort: `_ai` for an accent-insensitive sort, or `_ci` for a case-insensitive sort.

Example

`NLSSORT('Rumpelstiltskin')` returns the value `52756D70656C7374696C74736B696E00` for a default linguistic sort, which in this case is American.

### NULLIF

`NULLIF` compares one expression with another. It returns `NULL` when the expressions are equal, or the first expression when they are not.

Return Value

Data type of the first argument

Syntax

`NULLIF(expr1, expr2)`

Arguments

`expr1` is the base expression. It cannot be a literal null.

`expr2` is the expression to compare with the base expression.

Example

`NULLIF('red', 'Red')` returns the value `red`.

### NUMTODSINTERVAL

`NUMTODSINTERVAL` converts a number to an `INTERVAL DAY TO SECOND` data type.

Return Value

`INTERVAL DAY TO SECOND`

Syntax

`NUMTODSINTERVAL(n, 'interval_unit')`

Arguments

`n` can be any numeric expression.

`interval_unit` is a text expression that specifies the units. It must resolve to one of the following values:

• `DAY`

• `HOUR`

• `MINUTE`

• `SECOND`

These values are case insensitive.

Example

`NUMTODSINTERVAL(100, 'MINUTE')` returns the value +00 01:40:00.000000.

### NUMTOYMINTERVAL

`NUMTOYMINTERVAL` converts a number to an `INTERVAL YEAR TO MONTH` data type.

Return Value

`INTERVAL YEAR TO MONTH`

Syntax

`NUMTOYMINTERVAL(n, 'interval_unit')`

Arguments

`n` can be any numeric expression.

`interval_unit` is a text expression that specifies the units. It must resolve to one of the following values:

• `YEAR`

• `MONTH`

These values are case insensitive.

Example

`NUMTOYMINTERVAL(18, 'MONTH')` returns the value `+01-06`.

### NVL

`NVL` replaces a null with a string. `NVL` returns the replacement string when the base expression is null, and the value of the base expression when it is not null.

To replace an expression with one value if it is null and a different value if it is not, use `NVL2`.

Return Value

Data type of the first argument

Syntax

`NVL(expr1, expr2)`

Arguments

`expr1` is the base expression that is evaluated.

`expr2` is the replacement string that is returned when `expr1` is null.

Examples

`NVL('First String', 'Second String')` returns the value `First String`.

`NVL(null, 'Second String')` returns the value `Second String`.

### NVL2

`NVL2` returns one value when the value of a specified expression is not null, or another value when the value of the specified expression is null.

To replace a null value with a string, use `NVL`.

Return Value

Data type of the first argument

Syntax

`NVL2(expr1, expr2, expr3)`

Arguments

`expr1` is the base expression whose value this function evaluates.

`expr2` is an expression whose value is returned when `expr1` is not null.

`expr3` is an expression whose value is returned when `expr1` is null.

Example

`NVL2('Which string?', 'First String', 'Second String')` returns the value `First String`.

### ORA_HASH

`ORA_HASH` generates hash values for an expression. You can use it to randomly assign a set of values into several buckets for analysis, or to generate a set of random numbers.

Return Value

`NUMBER`

Syntax

`ORA_HASH  (expr [, max_bucket [, seed_value ] ])`

Arguments

`expr` can be any expression that provides the data for generating the hash values.

`max_bucket` is the maximum bucket number. For example, when `max_bucket` is set to 5, `ORA_HASH` returns values of 0 to 5, creating six buckets. Set this value from 0 to 4294967295 or 2^32-1 (default).

`seed_value` is a value used by `ORA_HASH` to generate the hash values. Enter a different `seed_value` for different results. Set this value from 0 (default) to 4294967295 or 2^32-1.

Example

`ORA_HASH(PRODUCT_CUBE.PRICES, 5)` returns a value in the range of 0 to 5 for each value of the Prices measure, as shown in the Hash 5 column. The rows are also sorted on the Hash 5 column.

`ORA_HASH(PRODUCT_CUBE.PRICES, 5, 13)` also returns values in the range of 0 to 5, but uses a different seed.

Product Prices Hash 5 Seed 13

ENVY STD

200539.83

0

4

ENVY EXE

255029.31

0

5

1GB USB DRV

44645.65

1

2

DLX MOUSE

1379.49

2

2

144MB DISK

3011.43

2

5

512 USB DRV

22139.99

2

2

19 SVGA

34837.16

3

0

56KPS MODEM

12478

3

2

ENVY EXT KBD

4312.22

3

5

17 SVGA

22605.55

4

1

EXT CD ROM

17990.14

4

0

ENVY ABM

205462.25

5

1

### POWER

`POWER` raises a number to a power.

Return Value

`NUMBER`

Syntax

`POWER(n2, n1)`

Arguments

`n2` is any numeric expression that is raised to a power.

`n1` is the exponent.

Example

`POWER(3,2)` returns the value `9`.

### RAWTOHEX

`RAWTOHEX` converts raw data to a character value containing its hexadecimal representation.

Return Value

`VARCHAR2`

Syntax

`RAWTOHEX(raw)`

Arguments

`raw` can be any scalar data type other than `LONG`, `LONG RAW`, `CLOB`, `BLOB`, or `BFILE`.

Example

`RAWTOHEX(NLSSORT('Rumpelstiltskin'))` converts the raw value returned by `NLSSORT` to the hexadecimal value `52756D70656C7374696C74736B696E00`.

### REGEXP_COUNT

`REGEXP_COUNT` searches a string for a regular pattern and returns the number of times the pattern occurs. If no match is found, the function returns 0.

The function evaluates strings using characters as defined by the input character set.

Return Value

`NUMBER`

Syntax

```REGEXP_COUNT (source_char, pattern
[, position
[, match_parameter ]
]
)```

Arguments

`source_char` is the text expression to search.

`pattern` is the string to search for. A period matches any character. For a list of operators, refer to the Oracle Database SQL Language Reference, Appendix D, "Oracle Regular Expression Support."

`position` is a nonzero integer indicating the character of `source_char` where the function begins the search. When `position` is negative, then the function counts and searches backward from the end of string. The default value of `position` is 1, which means that the function begins searching at the first character of `source_char`.

`match_parameter` is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values:

• `c`: Case-sensitive matching.

• `i`: Case-insensitive matching.

• `m`: Treat the source string as multiple lines. The function interprets ` ^` and `\$` as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.

• `n`: New-line character is among the characters matched by a period (the wildcard character). By default, it is not.

• `x`: Ignore whitespace characters.

Example

`REGEXP_COUNT('Mississippi', 'i', 1)` searches the string `Mississippi` for the letter `i`, beginning the search at the first letter. It returns the value `4`.

### REGEXP_REPLACE

`REGEXP_REPLACE` searches a string for a regular pattern and replaces it with another string. By default, the function returns `source_char` with every occurrence of the regular expression pattern replaced with `replace_string`.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

```REGEXP_REPLACE(source_char, pattern
[, replace_string
[, position
[, occurrence
[, match_parameter ]
]
]
]
)```

Arguments

`source_char` is the text expression that is searched.

`pattern` is the text expression to search for. It is usually a text literal and can contain up to 512 bytes. For a list of operators, refer to the Oracle Database SQL Language Reference, Appendix D, "Oracle Regular Expression Support."

`replace_string` is the text that replaces `pattern` in `source_char`.

`position` is a nonzero integer indicating the character of `source_char` where the function begins the search. When `position` is negative, then the function counts and searches backward from the end of string. The default value of `position` is 1, which means that the function begins searching at the first character of `source_char`.

`occurrence` is an integer indicating which occurrence of `pattern` the function should search for. The value of `occurrence` must be positive. The default values of `occurrence` is 1, meaning the function searches for the first occurrence of `pattern`.

`match_parameter` is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values:

• `c`: Case-sensitive matching.

• `i`: Case-insensitive matching.

• `m`: Treat the source string as multiple lines. The function interprets ` ^` and `\$` as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.

• `n`: New-line character is among the characters matched by a period (the wildcard character). By default, it is not.

• `x`: Ignore whitespace characters.

Example

`REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA', '( ){2,}', ' ')`

eliminates extra spaces and returns the string

`500 Oracle Parkway, Redwood Shores, CA`

### REGEXP_INSTR

`REGEXP_INSTR` searches a string for a regular pattern. It can return an integer indicating either the beginning or the ending position of the matched substring. If no match is found, then the function returns 0.

The function evaluates strings using characters as defined by the input character set.

Return Value

`NUMBER`

Syntax

```REGEXP_INSTR (source_char, pattern
[, position
[, occurrence
[, return_option
[, match_parameter ]
]
]
]
)```

Arguments

`source_char` is the text expression to search.

`pattern` is the string to search for. A period matches any character. For a list of operators, refer to the Oracle Database SQL Language Reference, Appendix D, Oracle Regular Expression Support.

`position` is a nonzero integer indicating the character of `source_char` where the function begins the search. When `position` is negative, then the function counts and searches backward from the end of string. The default value of `position` is 1, which means that the function begins searching at the first character of `source_char`.

`occurrence` is an integer indicating which occurrence of `pattern` the function should search for. The value of `occurrence` must be positive. The default values of `occurrence` is 1, meaning the function searches for the first occurrence of `pattern`.

`return_option` is either 0 to return the position of the match (default), or 1 to return the position of the character following the match.

`match_parameter` is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values:

• `c`: Case-sensitive matching.

• `i`: Case-insensitive matching.

• `m`: Treat the source string as multiple lines. The function interprets ` ^` and `\$` as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.

• `n`: New-line character is among the characters matched by a period (the wildcard character). By default, it is not.

• `x`: Ignore whitespace characters.

Example

`REGEXP_INSTR('Mississippi', 'i', 1, 3)` searches the string `Mississippi` for the third instance of the letter `i`, beginning the search at the first letter. It returns the value `8`.

### REGEXP_SUBSTR

`REGEXP_SUBSTR` searches a string for a pattern and returns the matching string.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR`

Syntax

```REGEXP_SUBSTR(source_char, pattern
[, position
[, occurrence
[, match_parameter ]
]
]
)```

Arguments

`source_char` is the text expression that is searched.

`pattern` is the text expression to search for. It is usually a text literal and can contain up to 512 bytes. For a list of operators, refer to the Oracle Database SQL Language Reference, Appendix D, "Oracle Regular Expression Support."

`position` is a nonzero integer indicating the character of `source_char` where the function begins the search. When `position` is negative, then the function counts and searches backward from the end of string. The default value of `position` is 1, which means that the function begins searching at the first character of `source_char`.

`occurrence` is an integer indicating which occurrence of `pattern` the function should search for. The value of `occurrence` must be positive. The default values of `occurrence` is 1, meaning the function searches for the first occurrence of `pattern`.

`match_parameter` is a text expression that lets you change the default matching behavior of the function. You can specify one or more of the following values:

• `c`: Case-sensitive matching.

• `i`: Case-insensitive matching.

• `m`: Treat the source string as multiple lines. The function interprets ` ^` and `\$` as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.

• `n`: New-line character is among the characters matched by a period (the wildcard character). By default, it is not.

• `x`: Ignore whitespace characters.

Examples

`REGEXP_SUBSTR('7 W 96th St, New York, NEW YORK', 'new york', 10, 2, 'i')` starts searching at the tenth character and matches `NEW YORK` in a case-insensitive match.

`REGEXP_SUBSTR('parsley, sage, rosemary, thyme', 's[^,]+e', 1, 2)` starts searching at the first character and matches the second substring consisting of the letter `s`, any number of characters that are not commas, and the letter `e`. In this example, the function returns the value `sage`.

### REMAINDER

`REMAINDER` returns a rounded remainder when one number is divided by another using this equation:

`n2 - (n1 * N)`

where `N` is the integer nearest `n2/n1`.

Return Value

`NUMBER`

Syntax

`REMAINDER(n2, n1)`

Arguments

`n1` is a numeric expression for the divisor.

`n2` is a numeric expression for the dividend.

Example

`REMAINDER(18,7)` returns the value `-3`.

### REPLACE

`REPLACE` searches a string for a regular pattern, replaces it with another string, and returns the modified string.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`REPLACE(char, search_string [, replacement_string ])`

Arguments

`char` is the text expression that is searched.

`search_string` is the text expression to search for.

`replacement_string` is the text expression that replaces `search_string` in `char`.

Example

`REPLACE('Nick Nack', 'N', 'Cl')` returns the string `Click Clack`.

### ROUND (date)

`ROUND` returns a date rounded to the unit specified by the date format.

Return Value

`DATE`

Syntax

`ROUND(date [, fmt ])`

Arguments

`date` is an expression that identifies a date and time.

`fmt` is a text literal with a date format, as described in the Oracle Database SQL Language Reference.

Examples

`ROUND(SYSDATE, 'YEAR')` returns the value `01-JAN-07` for any day in the last half of 2006.

`ROUND(TO_DATE('13-OCT-06'), 'MONTH')` returns the value `01-OCT-06`.

### ROUND (number)

`ROUND` returns a number rounded to a specified number of places.

Return Value

`NUMBER`

Syntax

`ROUND(n [, integer ])`

Arguments

`n` is the number to round.

`integer` is the number of decimal places of the rounded number. A negative value rounds to the left of the decimal point. The default value is 0.

Examples

`ROUND(15.193)` returns the value `15`.

`ROUND(15.193,1)` returns the value `15.2`.

`ROUND(15.193,-1)` returns the value `20`.

### ROWIDTOCHAR

`ROWIDTOCHAR` converts a row address from a `ROWID` data type to text.The return value is always 18 characters long in the database character set.

Return Value

`VARCHAR2`

Syntax

`ROWIDTOCHAR(rowid)`

Arguments

`rowid` is a row address to convert.

### ROWIDTONCHAR

`ROWIDTONCHAR` converts a row address from the `ROWID` data type to text. The return value is always 18 characters in the national character set.

Return Value

`NVARCHAR2`

Syntax

`ROWIDTONCHAR(rowid)`

Arguments

`rowid` is a row address to convert.

`RPAD` adds characters to the right of an expression to a specified length. The data type of the return value is the same as the original text.

Use `LPAD` to add characters to the left.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`RPAD(expr1 , n [, expr2 ])`

Arguments

`expr1` is a text expression for the base string.

`n` is the total length of the returned expression. If `expr1` is longer than `n`, then this function truncates `expr1` to `n` characters.

`expr2` is a text expression for the padding characters. By default, it is a space.

Example

`RPAD('Stay tuned', 15, '. ')` returns the value `Stay tuned. . .`

`RPAD('Stay tuned', 4)` returns the value `Stay`.

### RTRIM

`RTRIM` scans a text expression from right to left and removes all the characters that match the characters in the trim expression, until it finds an unmatched character. The data type of the return value is the same as the original text.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`RTRIM(char [, set ])`

Arguments

`char` is the text expression to be trimmed.

`set` is a text expression with the characters to remove. The default value of `set` is a single blank.

Examples

`RTRIM('You did that!?!?!?!', '?!')` returns the value `You did that`.

`RTRIM('3.14848', '84')` returns the text value `3.1`.

### SESSIONTIMEZONE

`SESSIONTIMEZONE` returns the time zone of the current session. The return type is a time zone offset from Coordinated Universal Time (UTC) or a time zone region name.

Return Value

`VARCHAR2`

Syntax

`SESSIONTIMEZONE`

Example

`SESSIONTIMEZONE` returns the value `-05:00` for Eastern Standard Time.

### SIGN

`SIGN` returns a value that indicates whether a specified number is less than, equal to, or greater than 0 (zero):

• `-1` if `n < 0`

• `0` if `n = 0`

• `1` if `n > 0`

Return Value

`NUMBER`

Syntax

`SIGN(n)`

Arguments

`n` is a numeric expression.

Example

`SIGN(-15)` returns the value `-1`.

### SIN

`SIN` returns the sine of an angle.

Return Value

`NUMBER`

Syntax

`SIN(n)`

Arguments

`n` is a numeric expression for an angle in radians.

Example

`SIN(30 * 3.1415927/180)` calculates the sine of a 30 degrees angle as the value `0.500000007`. The numeric expression converts degrees to radians.

### SINH

`SINH` returns the sine of a hyperbolic angle.

Return Value

`NUMBER`

Syntax

`SINH(n)`

Arguments

`n` is a numeric expression for a hyperbolic angle.

Example

`SINH(1)` returns the value `1.17520119`.

### SOUNDEX

`SOUNDEX` returns a character string containing the phonetic representation of a text expression. This function lets you compare words that are spelled differently but sound alike.

The function is based on the algorithm described in Donald Knuth's The Art of Computer Programming. This algorithm was designed specifically for English. Its results for other languages other than English are unpredictable and typically unsatisfactory.

Return Value

`VARCHAR2`

Syntax

`SOUNDEX (char)`

Arguments

`char` can be any text expression.

Example

All of these examples return the value `D500`:

```soundex('Donna')

soundex('Diane')

soundex('Dana')```

### SQRT

`SQRT` returns the square root of a number.

Return Value

`NUMBER`

Syntax

`SQRT(n)`

Arguments

`n` is a numeric expression for a positive number.

Example

`SQRT(13)` returns the value `3.60555128`.

### SUBSTR

`SUBSTR` returns a portion of string, beginning at a specified character position and extending a specified number of characters.

• `SUBSTR` calculates lengths using characters as defined by the input character set.

• `SUBSTRB` uses bytes instead of characters.

• `SUBSTRC` uses Unicode complete characters.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`{ SUBSTR | SUBSTRB | SUBSTRC }(char, position [, substring_length ])`

Arguments

`char` is a text expression that provides the base string from which the substring is derived.

`position` identifies the first character of the substring:

• When `position` is positive, then the function counts from the beginning of `char` to find the first character.

• When `position` is negative, then the function counts backward from the end of `char`.

• When `position` is 0 (zero), then the first character is the beginning of the substring.

`substring_length` is the number of characters in the returned string. By default, the function returns all characters to the end of the base string. If you specify a value less than 1, then the function returns a null.

Examples

`SUBSTR('firefly', 1, 4)` returns the substring `fire`.

`SUBSTR('firefly', -3, 3)` returns the substring `fly`.

### SYS_CONTEXT

`SYS_CONTEXT` returns the value of an attribute of a named context. The context, attribute, and value must already be defined in the database. If the context is not defined, `SYS_CONTEXT` returns `NULL`.

Return Value

`VARCHAR2`

Syntax

`SYS_CONTEXT ('namespace', 'parameter')`

Arguments

`namespace` can be any named context in the database. `USERENV` is a built-in context that describes the current session.

`parameter` is a defined attribute of `namespace`. Table 3-3 describes the predefined attributes of `USERENV` that are most likely to have values. For a complete list, refer to the `SYS_CONTEXT` entry in the Oracle Database SQL Language Reference.

Table 3-3 USERENV Attributes

USERENV Attribute Description

`AUTHENTICATED_IDENTITY`

The identity used for authentication, such as database user name, schema name, or operating system login name.

`AUTHENTICATION_METHOD`

The method of authentication, such as `PASSWORD`, `OS`, or `SSL`.

`CURRENT_EDITION_ID`

The session edition identifier, such as `100`.

`CURRENT_EDITION_NAME`

The session edition name, such as `ORA\$BASE`.

`CURRENT_SCHEMA`

The name of the currently active default schema, such as `SH`.

`CURRENT_SCHEMA_ID`

The numeric identifier of the currently active default schema, such as `80`.

`CURRENT_USER`

The name of the database user whose privileges are currently active, such as `SH`.

`CURRENT_USERID`

The numeric identifier of the database user whose privileges are currently active, such as `80`.

`DATABASE_ROLE`

Data Guard role of the database: `PRIMARY`, `PHYSICAL STANDBY`, `LOGICAL STANDBY`, or `SNAPSHOT STANDBY`.

`DB_DOMAIN`

The network domain of the database as specified by the `DB_DOMAIN` initialization parameter, such as `us.example.com`.

`DB_NAME`

The name of the database as specified by the `DB_NAME` initialization parameter.

`DB_UNIQUE_NAME`

The unique name of the database within the domain as specified by the `DB_UNIQUE_NAME` initialization parameter.

`ENTERPRISE_IDENTITY`

The enterprise-wide identity of the user, or `NULL` for local users, SYSDBA, and SYSOPER.

`FG_JOB_ID`

Job identifier of the current session if a client foreground process opened it; otherwise, `NULL`.

`GLOBAL_CONTEXT_MEMORY`

The number used in the System Global Area by the globally accessed context.

`GLOBAL_UID`

The global user identification from Oracle Internet Directory for Enterprise User Security logins; otherwise, `NULL`.

`HOST`

The name of the client host computer.

`IDENTIFICATION_TYPE`

The way the user schema was created in the database: `LOCAL`, `EXTERNAL`, `GLOBAL SHARED`, or `GLOBAL PRIVATE`.

`INSTANCE`

The identification number of the current instance, such as `1`.

`INSTANCE_NAME`

The name of the database instance.

`IP_ADDRESS`

The IP address of the client, such as `10.255.255.255`.

`ISDBA`

`TRUE` if the user was authenticated with DBA privileges; otherwise, `FALSE`.

`LANG`

A short name for the session language, such as `US` for `AMERICAN`.

`LANGUAGE`

The language, territory, and database character set in the form `language_territory.characterset`, such as `AMERICA_AMERICAN.WE8DEC`.

`MODULE`

The application name set through the `DBMS_APPLICATION_INFO` package or OCI, such as `JDBC Thin Client` or `SQL Developer`.

`NETWORK_PROTOCOL`

The network protocol being used for communication, such as `TCP`.

`NLS_CALENDAR`

The session calendar, such as `GREGORIAN`.

`NLS_CURRENCY`

The session currency mark, such as `\$`.

`NLS_DATE_FORMAT`

The session date format, such as `DD-MON-RR`.

`NLS_DATE_LANGUAGE`

The session date language, such as `AMERICAN`.

`NLS_SORT`

`BINARY` or a linguistic sort basis, such as `XSPANISH`.

`NLS_TERRITORY`

The session territory, such as `AMERICA`.

`OS_USER`

The operating system user name of the client process that initiated the database session.

`SERVER_HOST`

The host name of the computer where the database instance is running.

`SERVICE_NAME`

The name of the service the session is connected to, such as `SYS\$USERS`.

`SESSION_USER`

The database user name or schema name that identified the user at login, such as `SH`.

`SESSIONID`

The session identifier, such as `120456`.

`SID`

The session number, such as `86`.

Example

`SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')` returns a value such as `DD-MON-RR`.

### SYSDATE

`SYSDATE` returns the current date and time of the operating system on which the database resides. The format of the value depends on the value of the `NLS_DATE_FORMAT` initialization parameter.

Return Value

`DATE`

Syntax

`SYSDATE`

Examples

`SYSDATE` returns a value such as `13-AUG-06` with `NLS_DATE_FORMAT` set to `DD-MON-RR`.

`TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')` returns a value such as `08-13-2006 17:20:47`. The date format provided in the call to `TO_CHAR` displays both the date and the time.

### SYSTIMESTAMP

`SYSTIMESTAMP` returns the system date, including fractional seconds and time zone, of the system on which the database resides.

Return Value

`TIMESTAMP WITH TIME ZONE`

Syntax

`SYSTIMESTAMP`

Example

`SYSTIMESTAMP` returns a value such as

`13-AUG-06 05.28.10.385799 PM -08:00`.

### TAN

`TAN` returns the tangent of an angle.

Return Value

`NUMBER`

Syntax

`TAN(n)`

Arguments

`n` is a numeric expression for an angle in radians.

Example

`TAN(135 * 3.1415927/180)` calculates the tangent of a 135 degree angle as the value `-0.99999993`. The expression converts degrees to radians.

### TANH

`TANH` returns the tangent of a hyperbolic angle.

Return Value

`NUMBER`

Syntax

`TANH(n)`

Arguments

`n` is a numeric expression for a hyperbolic angle.

Example

`TANH(.5)` returns the value `0.462117157`.

### TO_BINARY_DOUBLE

`TO_BINARY_DOUBLE` converts a text or numeric expression to a double-precision floating-point number.

Return Value

`BINARY_DOUBLE`

Syntax

`TO_BINARY_DOUBLE (expr [, fmt [, 'nlsparam' ] ])`

Arguments

`n` can be any text or numeric expression.

`fmt` is a text expression that identifies a number format model as described in the Oracle Database SQL Language Reference.

`nlsparam` specifies the characters used by these number format elements:

• Decimal character

• Group separator

• Local currency symbol

• International currency symbol

This argument has the format shown here:

```'NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = territory '
```

The `d` is the decimal character, and the `g` is the group separator. They must be different single-byte characters. Within the quoted string, use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.

If you omit this argument or any of the NLS parameters, then this function uses the default values for your session to interpret `expr`.

Example

All of these examples return the value `1.235E+003`:

```TO_BINARY_DOUBLE(1234.56)

TO_BINARY_DOUBLE('\$1,234.56', '\$9,999.99')

TO_BINARY_DOUBLE('1.234,56', '9G999D99', 'NLS_NUMERIC_CHARACTERS='',.''')```

### TO_BINARY_FLOAT

`TO_BINARY_FLOAT` converts a text or numeric expression to a single-precision floating-point number.

Return Value

`BINARY_FLOAT`

Syntax

`TO_BINARY_FLOAT (expr [, fmt [, 'nlsparam' ] ])`

Arguments

`n` can be any text or numeric expression.

`fmt` is a text expression that identifies a number format model as described in the Oracle Database SQL Language Reference.

`nlsparam` specifies the characters used by these number format elements:

• Decimal character

• Group separator

• Local currency symbol

• International currency symbol

This argument has the format shown here:

```'NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = territory '
```

The `d` is the decimal character, and the `g` is the group separator. They must be different single-byte characters. Within the quoted string, use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.

If you omit this argument or any of the NLS parameters, then this function uses the default values for your session to interpret `expr`.

Examples

All of these examples return the value `1.235E+003`:

```TO_BINARY_FLOAT(1234.56)

TO_BINARY_FLOAT('\$1,234.56', '\$9,999.99')

TO_BINARY_FLOAT('1.234,56', '9G999D99', 'NLS_NUMERIC_CHARACTERS='',.''')```

### TO_CHAR (character)

`TO_CHAR(character)` converts a text expression to the database character set.

Return Value

`VARCHAR2`

Syntax

`TO_CHAR(exp)`

Arguments

`char` is a text expression. If it contains characters that are not represented in the database character set, then the conversion results in a loss of data.

Examples

`TO_CHAR('¡Una qué sorpresa!')` returns the value `?Una qu? sorpresa!` Two letters are lost in the conversion (`¡`and `é`) because they are not in the database character set.

`TO_CHAR('David Ortiz')` returns the value `David Ortiz` in the database character set. No characters are lost in this conversion because all of them are in the database character set.

### TO_CHAR (datetime)

`TO_CHAR(datetime)` converts a datetime or interval expression to a text string in a specified format.

Return Value

`VARCHAR2`

Syntax

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

Arguments

`datetime` is a datetime expression to be converted to text.

`interval` is an interval expression to be converted to text.

`fmt` is a datetime model format specifying the format of `char`. The default date format is determined implicitly by the `NLS_TERRITORY` initialization parameter or can be set explicitly by the `NLS_DATE_FORMAT` parameter. For data type formats, refer to the Oracle Database SQL Language Reference.

`nlsparam` specifies the language in which month and day names and abbreviations are returned. This argument can have this form:

```'NLS_DATE_LANGUAGE = language'
```

By default, the return value is in the session date language.

Examples

`TO_CHAR(SYSDATE)` returns a value such as `11-APR-08`.

`TO_CHAR(SYSDATE, 'Day: MONTH DD, YYYY')` returns a value such as `Friday : APRIL 11, 2008`.

`TO_CHAR(SYSDATE, 'Day: MONTH DD, YYYY', 'NLS_DATE_LANGUAGE = Spanish')` returns a value such as `Viernes : ABRIL 11, 2008`.

### TO_CHAR (number)

`TO_CHAR(number)` converts a numeric expression to a text value in the database character set.

Return Value

`VARCHAR2`

Syntax

`TO_CHAR(n [, fmt [, 'nlsparam' ] ])`

Arguments

`n` is a numeric expression to be converted.

`fmt` is a text expression that identifies a number format model as described in the Oracle Database SQL Language Reference.

`nlsparam` specifies the characters that are returned by these number format elements:

• Decimal character

• Group separator

• Local currency symbol

• International currency symbol

This argument has the format shown here:

```'NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = territory '
```

The characters `d` and `g` represent the decimal character and group separator, respectively. They must be different single-byte characters. Within the quoted string, use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.

If you omit this argument or any of the NLS parameters, then this function uses the default values for your session.

Examples

`TO_CHAR(1234567, 'C9G999G999D99')` returns a text string such as `USD1,234,567.00`.

`TO_CHAR(1234567, 'C9G999G999D99', 'NLS_ISO_CURRENCY = SPAIN')` returns the text string `EUR1,234,567.00`.

### TO_DATE

`TO_DATE` converts a text expression to a `DATE` data type.

Return Value

`DATE`

Syntax

`TO_DATE(char [, fmt [, 'nlsparam' ] ])`

Arguments

`char` is a text expression that represents a date.

`fmt` is a datetime model format specifying the format of `char`. The default date format is determined implicitly by the `NLS_TERRITORY` initialization parameter or can be set explicitly by the `NLS_DATE_FORMAT` parameter. For data type formats, refer to the Oracle Database SQL Language Reference.

`nlsparam` specifies the language of `char`. This argument can have this form:

```'NLS_DATE_LANGUAGE = language'
```

By default, `char` is in the session date language.

Examples

`TO_DATE('October 13, 2008', 'MONTH DD, YYYY')` returns the value `13-OCT-08`.

`TO_DATE('13 Octubre 2008', 'dd month yyyy', 'NLS_DATE_LANGUAGE=SPANISH')` also returns the value `13-OCT-08`

### TO_DSINTERVAL

`TO_DSINTERVAL` converts a text expression to an `INTERVAL DAY TO SECOND` data type.

Return Value

`INTERVAL DAY TO SECOND`

Syntax

`TO_DSINTERVAL(char)`

Arguments

`char` is a text expression to be converted.

Example

`TO_DSINTERVAL('360 12:45:49')` returns the value `+360 12:45:49.000000`.

### TO_NCHAR (character)

`TO_NCHAR(character)` converts a character string to the national character set.

Return Value

`NVARCHAR2`

Syntax

`TO_NCHAR(exp)`

Arguments

`exp` is a text expression. If it contains characters that are not represented in the national character set, then the conversion results in a loss of data.

Example

`TO_NCHAR('David Ortiz')` returns the value `David Ortiz` in the national character set.

### TO_NCHAR (datetime)

`TO_NCHAR(datetime)` converts a datetime or interval value to the national character set.

Return Value

`NVARCHAR2`

Syntax

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

Arguments

`datetime` is a datetime expression to be converted to text.

`interval` is an interval expression to be converted to text.

`fmt` is a datetime model format specifying the format of `char`. The default date format is determined implicitly by the `NLS_TERRITORY` initialization parameter or can be set explicitly by the `NLS_DATE_FORMAT` parameter. For data type formats, refer to the Oracle Database SQL Language Reference.

`nlsparam` specifies the language in which month and day names and abbreviations are returned. This argument can have this form:

```'NLS_DATE_LANGUAGE = language'
```

By default, the return value is in the session date language.

Examples

`TO_NCHAR(SYSDATE)` returns a value such as `11-APR-08`.

`TO_NCHAR(SYSDATE, 'Day: MONTH DD, YYYY')` returns a value such as `Friday : APRIL 11, 2008`.

`TO_NCHAR(SYSDATE, 'Day: MONTH DD, YYYY', 'NLS_DATE_LANGUAGE = Spanish')` returns a value such as `Viernes : ABRIL 11, 2008`.

### TO_NCHAR (number)

`TO_NCHAR(number)` converts a number to the national character set.

Return Value

`NVARCHAR2`

Syntax

`TO_CHAR(n [, fmt [, 'nlsparam' ] ])`

Arguments

`n` is a numeric expression to be converted.

`fmt` is a text expression that identifies a number format model as described in the Oracle Database SQL Language Reference.

`nlsparam` is a text expression that specifies the characters that are returned by these number format elements:

• Decimal character

• Group separator

• Local currency symbol

• International currency symbol

This argument has the format shown here:

```'NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = territory '
```

The characters `d` and `g` represent the decimal character and group separator, respectively. They must be different single-byte characters. Within the quoted string, use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.

If you omit this argument or any of the NLS parameters, then this function uses the default values for your session.

Examples

`TO_NCHAR(1234567, 'C9G999G999D99')` returns a text string such as `USD1,234,567.00`.

`TO_NCHAR(1234567, 'C9G999G999D99', 'NLS_ISO_CURRENCY = SPAIN')` returns the text string `EUR1,234,567.00`.

### TO_NUMBER

`TO_NUMBER` converts a text expression containing a number to a value of `NUMBER` data type.

Return Value

`NUMBER`

Syntax

`TO_NUMBER(expr [, fmt [, 'nlsparam' ] ])`

Arguments

`expr` is an expression to be converted to a number.

`fmt` is a text expression that identifies a number format model as described in the Oracle Database SQL Language Reference.

`nlsparam` specifies the characters used by these number format elements:

• Decimal character

• Group separator

• Local currency symbol

• International currency symbol

This argument has the format shown here:

```'NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = territory '
```

The `d` is the decimal character, and the `g` is the group separator. They must be different single-byte characters. Within the quoted string, use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.

If you omit this argument or any of the NLS parameters, then this function uses the default values for your session to interpret `expr`.

Examples

`TO_NUMBER('\$1,234,567.89', 'L999G999G999D99')` returns the value `1234567.89`.

`TO_NUMBER('EUR1,234,567.89', 'C999G999G999D99', 'NLS_ISO_CURRENCY=SPAIN')` also returns the value `1234567.89`.

### TO_TIMESTAMP

`TO_TIMESTAMP` converts a text expression to a value of `TIMESTAMP`.

Return Value

`TIMESTAMP`

Syntax

`TO_TIMESTAMP(char [, fmt [ 'nlsparam' ] ])`

Arguments

`char` is a text expression to be converted.

`fmt` is a datetime model format specifying the format of `char`. The default date format is determined implicitly by the `NLS_TERRITORY` initialization parameter or can be set explicitly by the `NLS_DATE_FORMAT` parameter. For data type formats, refer to the Oracle Database SQL Language Reference.

`nlsparam` specifies the language in which month and day names and abbreviations given in `char`. This argument has this form:

```'NLS_DATE_LANGUAGE = language'
```

By default, `char` is in the session date language.

Examples

`TO_TIMESTAMP('10-SEP-0614:10:10.123000','DD-MON-RRHH24:MI:SS.FF')` returns the value `10-SEP-06 02.10.10.123000 PM`.

`TO_TIMESTAMP('10-AGOSTO-0714:10:10', 'DD-MON-RRHH24:MI:SS.FF', 'NLS_DATE_LANGUAGE=SPANISH')` returns the value `10-AUG-07 02.10.10.000000 PM`.

### TO_TIMESTAMP_TZ

`TO_TIMESTAMP_TZ` converts a text expression to a value of `TIMESTAMP``WITH``TIME``ZONE` data type.

Return Value

`TIMESTAMP WITH TIME ZONE`

Syntax

`TO_TIMESTAMP_TZ(char [, fmt [ 'nlsparam' ] ])`

Arguments

`char` is a text expression to be converted.

`fmt` is a datetime model format specifying the format of `char`. The default date format is determined implicitly by the `NLS_TERRITORY` initialization parameter or can be set explicitly by the `NLS_DATE_FORMAT` parameter. For data type formats, refer to the Oracle Database SQL Language Reference.

`nlsparam` specifies the language in which month and day names and abbreviations given in `char`. This argument has this form:

```'NLS_DATE_LANGUAGE = language'
```

By default, `char` is in the session date language.

Examples

`TO_TIMESTAMP_TZ('2006-03-26 7:33:00 -4:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM')` returns the value `26-MAR-06 07.33.00.000000 AM -04:00`.

`TO_TIMESTAMP_TZ('2006-AGOSTO-13 7:33:00 -4:00', 'YYYY-MONTH-DD HH:MI:SS TZH:TZM', 'NLS_DATE_LANGUAGE=SPANISH')` returns the value `13-AUG-06 07.33.00.000000 AM -04:00`.

### TO_YMINTERVAL

`TO_YMINTERVAL` converts a text expression to an `INTERVAL YEAR TO MONTH` data type. The function accepts argument in one of the two formats:

• SQL interval format compatible with the SQL standard (ISO/IEC 9075:2003)

• ISO duration format compatible with the ISO 8601:2004 standard

Return Value

`INTERVAL YEAR TO MONTH`

Syntax

```TO_YMINTERVAL  ( ' { sql_format | ym_iso_format } ' )

sql_format::=
[+|-] years - months

ym_iso_format::=
[-] P  [ years Y  ] [months M ] [days D ] [ T  [hours H ] [minutes M ] [seconds [. frac_secs] S  ] ]```

Arguments

In SQL format:

`years` is an integer between 0 and 999999999

`months` is an integer between 0 and 11.

Additional blanks are allowed between format elements.

In ISO format:

`years` and `months` are integers between 0 and 999999999.

`days`, `hours`, `minutes`, `seconds`, and `frac_secs` are nonnegative integers and are ignored.

No blanks are allowed in the value.

Examples

`TO_YMINTERVAL('1-6')` and `TO_YMINTERVAL('P1Y6M')` return the value `+01-06` for 1 year and 6 months.

`SYSDATE + TO_YMINTERVAL('1-6')` adds one year and six months to the current date. When `SYSDATE` is `15-APR-08`, the value is `15-OCT-09`.

`SYSDATE + TO_YMINTERVAL('P1Y6M')` adds one year and six months to the current date using ISO format. When `SYSDATE` is `15-APR-08`, the value is `15-OCT-09`.

`SYSDATE + TO_YMINTERVAL('-1-2')` subtracts one year and two months from the current date. When `SYSDATE` is `15-APR-08`, the value is `15-FEB-07`.

### TRANSLATE

`TRANSLATE` enables you to make several single-character, one-to-one substitutions in one operation. This expression returns an expression with all occurrences of each character in one string replaced by its corresponding character in a second string.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`TRANSLATE(expr, from_string, to_string)`

Arguments

`expr` is a text expression to be modified.

`from_string` consists of one or more characters to be replaced in `expr`.

`to_string` consists of one or more characters that replace the corresponding characters in `from_string`. This string can be shorter than `from_string`, so that a null replaces the extra characters. However, `to_string` cannot be empty.

Example

`TRANSLATE('disk', 'dk', 'Dc')` returns the value `Disc`.

### TRANSLATE (USING)

`TRANSLATE` converts a text string between the database character set and the national character set.

Note: The `TRANSLATE USING` function is supported primarily for ANSI compatibility. Oracle recommends that you use the `TO_CHAR` and `TO_NCHAR` functions for converting data to the database or national character sets. `TO_CHAR` and `TO_NCHAR` can take as arguments a greater variety of data types than `TRANSLATE USING`, which accepts only character data.

Return Value

```VARCHAR2 | NVARCHAR2
```

Syntax

```TRANSLATE (char USING { CHAR_CS | NCHAR_CS } )
```

Arguments

`char` is a text expression to be converted to the database character set (`USING CHAR_CS`) or the national character set (`USING NCHAR_CS`).

Example

`TRANSLATE('north by northwest' USING NCHAR_CS)` returns the value `north by northwest` in the national character set.

### TRIM

`TRIM` removes leading or trailing characters (or both) from a character string.

Return Value

```VARCHAR2
```

Syntax

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

Arguments

`LEADING` removes matching characters from the beginning of the string.

`TRAILING` removes matching characters from the end of the string.

`BOTH` removes matching characters from both the beginning and the end of the string. (Default)

`trim_character` is a single character to be removed. By default, it is a space.

`trim_source` is the text expression to be trimmed.

Examples

`TRIM('0' FROM '00026501.6703000')` returns the value `26501.6703`.

`TRIM(LEADING '!' FROM '!!Help! Help!!')` returns the value `Help! Help!!`.

### TRUNC (number)

`TRUNC` shortens a numeric expression to a specified number of decimal places.

Return Value

`NUMBER`

Syntax

`TRUNC(n1 [, n2 ])`

Arguments

`n1` is the numeric expression to be truncated.

`n2` is the number of decimal places. A positive number truncates digits to the right of the decimal point, and a negative number replaces digits to the left of the decimal point. The default value is zero (0).

Examples

`TRUNC(15.79)` returns the value `15`.

`TRUNC(15.79, 1)` returns the value `15.7`.

`TRUNC(15.79, -1)` returns the value `10`.

### TZ_OFFSET

`TZ_OFFSET` returns the time zone offset from Coordinated Universal Time (UTC).

Return Value

`VARCHAR2`

Syntax

```TZ_OFFSET({ 'time_zone_name' | '{ + | - } hh:mi'
| SESSIONTIMEZONE | DBTMEZONE
}
)```

Arguments

`time_zone_name` is the name of a time zone.

`hh:mm` are hours and minutes. This argument simply returns itself.

`SESSIONTIMEZONE` returns the session time zone.

`DBTIMEZONE` returns the database time zone.

Examples

`TZ_OFFSET('US/Eastern')` returns the offset `-04:00` during Daylight Savings Time.

`TZ_OFFSET('EST')` returns the offset `-05:00`.

`TZ_OFFSET(DBTIMEZONE)` returns the offset `-07:00` for Mountain Standard Time.

### UID

`UID` returns a unique identifier (UID) for the session user (the user who logged on).

Return Value

`INTEGER`

Syntax

`UID`

Example

`UID` returns a value such as `76`.

### UNISTR

`UNISTR` converts a text string to the national character set.

Return Value

`NVARCHAR2`

Syntax

`UNISTR ( string )`

Arguments

`string` can be any text expression. For portability, Oracle recommends using only ASCII characters and Unicode encoding values as text literals. A Unicode encoding value has the form \xxxx where xxxx is the hexadecimal value of a character. Supplementary characters are encoded as two code units, the first from the high-surrogates range (U+D800 to U+DBFF), and the second from the low-surrogates range (U+DC00 to U+DFFF). To include a literal backslash in the string, precede it with another backslash (\\).

Example

`UNISTR('abc\00e5\00f1\00f6')` returns the value `abcåñö`.

### UPPER

`UPPER` converts all alphabetic characters in a text expression to uppercase. The data type of the return value is the same as the original text.

Return Value

`CHAR | NCHAR | VARCHAR2 | NVARCHAR2`

Syntax

`UPPER(char)`

Arguments

`char` can be any text expression.

Example

`UPPER('This is an emergency')` returns the string `THIS IS AN EMERGENCY`.

### USER

`USER` returns the name of the session user (the user who logged on).

Return Value

`VARCHAR2`

Syntax

`USER`

Example

`USER` returns a value such as `GLOBAL`.

### VSIZE

`VSIZE` returns the number of bytes in the internal representation of an expression. It returns `NULL` for a null expression.

Return Value

`NUMBER`

Syntax

`VSIZE (expr)`

Arguments

`expr` can be an expression of any data type.

Example

`VSIZE('Sound of thunder')` returns the value `16`.

`VSIZE(CHANNEL.LONG_DESCRIPTION)` returns the following values:

Channel VSIZE

Catalog

7

Direct Sales

12

Internet

8

### WIDTH_BUCKET

`WIDTH_BUCKET` enables you to construct a histogram range divided into intervals of identical size. The function returns the bucket number into which the value of an expression falls.

When needed, `WIDTH_BUCKET` creates an underflow bucket numbered 0 and an overflow bucket numbered `num_buckets`+1. These buckets handle values outside the specified range and are helpful in checking the reasonableness of the end points.

Return Value

`NUMBER`

Syntax

```WIDTH_BUCKET
(expr, min_value, max_value, num_buckets)```

Arguments

`expr` is the expression for which the histogram is being created. This expression must evaluate to a numeric or datetime value or to a value. If `expr` evaluates to null, then the function returns `NULL`.

`min_value` and `max_value` are expressions for the end points of the acceptable range for `expr`. Both of these expressions must evaluate to numeric or datetime values, and neither can evaluate to null.

`num_buckets` is an expression for the number of buckets. This expression must evaluate to a positive integer.

Example

`WIDTH_BUCKET(13, 0, 20, 4)` returns the value `3`. It creates four buckets from 0 to 20 and sorts the value 13 into bucket 3.

`WIDTH_BUCKET(-5, 0, 20, 4)` returns the value `0`. The value `-5` is below the beginning of the range.