ORACLE/PLSQL: DENSE_RANK FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL DENSE_RANK function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL DENSE_RANK function returns the rank of a row in a group of rows. It is very similar to the RANK function. However, the RANK function can cause non-consecutive rankings if the tested values are the same. Whereas, the DENSE_RANK function will always result in consecutive rankings.
The DENSE_RANK function can be used two ways - as an Aggregate function or as an Analytic function.
DENSE_RANK FUNCTION SYNTAX #1 - USED AS AN AGGREGATE FUNCTION
As an Aggregate function, the DENSE_RANK function returns the dense rank of a row within a group of rows.
The syntax for the DENSE_RANK function when used as an Aggregate function is:
DENSE_RANK( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )
Parameters or Arguments
expression1 .. expression_n can be one or more expressions which identify a unique row in the group.
NOTE
- There must be the same number of expressions in the first expression list as there is in the ORDER BY clause.
- The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.
APPLIES TO
The DENSE_RANK function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
EXAMPLE (AS AN AGGREGATE FUNCTION)
Let's look at some Oracle DENSE_RANK function examples and explore how to use the DENSE_RANK function in Oracle/PLSQL.
For example:
select DENSE_RANK(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from employees;
The SQL statement above would return the dense rank of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.
DENSE_RANK FUNCTION SYNTAX #2 - USED AS AN ANALYTIC FUNCTION
As an Analytic function, the DENSE_RANK function returns the rank of each row of a query with respective to the other rows.
The syntax for the DENSE_RANK function when used as an Analytic function is:
DENSE_RANK() OVER ( [ query_partition_clause] ORDER BY clause )
APPLIES TO
The DENSE_RANK function can be used in the following versions of Oracle/PLSQL:
- Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE (AS AN ANALYTIC FUNCTION)
Let's look at some Oracle DENSE_RANK function examples and explore how to use the DENSE_RANK function in Oracle/PLSQL.
For example:
select employee_name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary) from employees where department = 'Marketing';
The SQL statement above would return all employees who work in the Marketing department and then calculate a rank for each unique salary in the Marketing department. If two employees had the same salary, the DENSE_RANK function would return the same rank for both employees.
ORACLE/PLSQL: LN FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL LN function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL LN function returns the natural logarithm of a number.
SYNTAX
The syntax for the Oracle/PLSQL LN function is:
LN( number )
Parameters or Arguments
number must be greater than 0.
APPLIES TO
The LN function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle LN function examples and explore how to use the LN function in Oracle/PLSQL.
For example:
LN(20) Result: 2.99573227355399 LN(25) Result: 3.2188758248682 LN(100) Result: 4.60517018598809 LN(100.5) Result: 4.61015772749913
ORACLE/PLSQL: LOG FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL LOG function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL LOG function returns the logarithm of n base m.
SYNTAX
The syntax for the Oracle/PLSQL LOG function is:
LOG( m, n )
Parameters or Arguments
m must be a positive number, except 0 or 1.
n must be a positive number.
APPLIES TO
The LOG function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle LOG function examples and explore how to use the LOG function in Oracle/PLSQL.
For example:
LOG(10, 20) Result: 1.30102999566398 LOG(2, 15) Result: 3.90689059560852 LOG(100, 1) Result: 0
ORACLE/PLSQL: STDDEV FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL STDDEV function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL STDDEV function returns the standard deviation of a set of numbers.
The STDDEV function can be used two ways - as an Aggregate function or as an Analytic function.
STDDEV FUNCTION SYNTAX #1 - USED AS AN AGGREGATE FUNCTION
The syntax for the STDDEV function when used as an Aggregate function is:
stddev( [ DISTINCT | ALL ] expression )
Parameters or Arguments
expression is a numeric value or formula.
APPLIES TO
The STDDEV function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE (AS AN AGGREGATE FUNCTION)
Let's look at some Oracle STDDEV function examples and explore how to use the STDDEV function in Oracle/PLSQL.
For example:
select STDDEV(bonus) from employees;
The SQL statement above would return the standard deviation of the bonus field in the employees table.
STDDEV FUNCTION SYNTAX #2 - USED AS AN ANALYTIC FUNCTION
The syntax for the STDDEV function when used as an Analytic function is:
STDDEV( [ DISTINCT | ALL ] expression ) [ OVER ( analytical_clause ) ]
APPLIES TO
The STDDEV function can be used in the following versions of Oracle/PLSQL:
- Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE (AS AN ANALYTIC FUNCTION)
select employee_name, bonus, STDDEV(bonus) OVER (ORDER BY salary) from employees where department = 'Marketing';
The SQL statement above would return the cumulative standard deviation of the bonuses in the Marketing department in the employees table, ordered by salary.
ORACLE/PLSQL: VAR_POP FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL VAR_POP function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL VAR_POP function returns the population variance of a set of numbers.
SYNTAX
The syntax for the Oracle/PLSQL VAR_POP function is:
VAR_POP( expression )
Parameters or Arguments
expression is a numeric expression.
NOTE
- The VAR_POP function eliminates all null values before performing its calculations.
APPLIES TO
The VAR_POP function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle VAR_POP function examples and explore how to use the VAR_POP function in Oracle/PLSQL.
For example:
select VAR_POP(data_length) from all_tab_columns;
ORACLE/PLSQL: VAR_SAMP FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL VAR_SAMP function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL VAR_SAMP function returns the sample variance of a set of numbers.
SYNTAX
The syntax for the Oracle/PLSQL VAR_SAMP function is:
VAR_SAMP( expression )
Parameters or Arguments
expression is a numeric expression.
NOTE
- The VAR_SAMP function eliminates all null values before performing its calculations.
APPLIES TO
The VAR_SAMP function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle VAR_SAMP function examples and explore how to use the VAR_SAMP function in Oracle/PLSQL.
For example:
select VAR_SAMP(char_length) from all_tab_columns;
ORACLE/PLSQL: VARIANCE FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL VARIANCE function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL VARIANCE function returns the variance of a set of numbers.
SYNTAX
The syntax for the Oracle/PLSQL VARIANCE function is:
VARIANCE( expression )
Parameters or Arguments
expression is a numeric expression.
APPLIES TO
The VARIANCE function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle VARIANCE function examples and explore how to use the VARIANCE function in Oracle/PLSQL.
For example:
select VARIANCE(char_length) from all_tab_columns;
ORACLE/PLSQL: INSTRB FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL INSTRB function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL INSTRB function returns the location of a substring in a string, using bytes instead of characters.
SYNTAX
The syntax for the Oracle/PLSQL INSTRB function is:
INSTRB( string, substring [, start_position [, nth_appearance ] ] )
Parameters or Arguments
string is the string to search. string can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
substring is the substring to search for in string. substring can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
start_position is the position in string where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the INSTRB function counts back start_position number of characters from the end of string and then searches towards the beginning of string.
nth_appearance is the nth appearance of substring. This is optional. If omitted, it defaults to 1.
NOTE
- If substring is not found in string, then the INSTRB function will return 0.
APPLIES TO
The INSTRB function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle INSTRB function examples and explore how to use the INSTRB function in Oracle/PLSQL.
For example:
INSTRB('TechOnTheNet.com', 'e')
Result: 2 (the first occurrence of 'e' - single-byte character set)
INSTRB('TechOnTheNet.com', 'e', 1, 1)
Result: 2; (the first occurrence of 'e' - single-byte character set)
INSTRB('TechOnTheNet.com', 'e', 1, 2)
Result: 9; (the second occurrence of 'e' - single-byte character set)
INSTRB('TechOnTheNet.com', 'e', 1, 3)
Result: 11; (the third occurrence of 'e' - single-byte character set)
INSTRB('TechOnTheNet.com', 'e', -3, 2)
Result: 9 (single-byte character set)
ORACLE/PLSQL: INSTRC FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL INSTRC function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL INSTRC function returns the location of a substring in a string, using Unicode complete characters.
SYNTAX
The syntax for the Oracle/PLSQL INSTRC function is:
INSTRC( string, substring [, start_position [, nth_appearance ] ] )
Parameters or Arguments
string is the string to search. string can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. string can not be CLOB or NCLOB.
substring is the substring to search for in string. substring can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
start_position is the position in string where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the INSTRC function counts back start_position number of characters from the end of string and then searches towards the beginning of string.
nth_appearance is the nth appearance of substring. This is optional. If omitted, it defaults to 1.
NOTE
- If substring is not found in string, then the INSTRC function will return 0.
APPLIES TO
The INSTRC function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle INSTRC function examples and explore how to use the INSTRC function in Oracle/PLSQL.
For example:
INSTRC('TechOnTheNet.com', 'e')
Result: 2 (the first occurrence of 'e')
INSTRC('TechOnTheNet.com', 'e', 1, 1)
Result: 2 (the first occurrence of 'e')
INSTRC('TechOnTheNet.com', 'e', 1, 2)
Result: 9 (the second occurrence of 'e')
INSTRC('TechOnTheNet.com', 'e', 1, 3)
Result: 11 (the third occurrence of 'e')
INSTRC('TechOnTheNet.com', 'e', -3, 2)
Result: 9
ORACLE/PLSQL: INSTR2 FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL INSTR2 function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL INSTR2 function returns the location of a substring in a string, using UCS2 code points.
SYNTAX
The syntax for the Oracle/PLSQL INSTR2 function is:
INSTR2( string, substring [, start_position [, nth_appearance ] ] )
Parameters or Arguments
string is the string to search. string can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. string can not be CLOB or NCLOB.
substring is the substring to search for in string. substring can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
start_position is the position in string where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the INSTR2 function counts back start_position number of characters from the end of string and then searches towards the beginning of string.
nth_appearance is the nth appearance of substring. This is optional. If omitted, it defaults to 1.
NOTE
- If substring is not found in string, then the INSTR2 function will return 0.
APPLIES TO
The INSTR2 function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle INSTR2 function examples and explore how to use the INSTR2 function in Oracle/PLSQL.
For example:
INSTR2('TechOnTheNet.com', 'e')
Result: 2 (the first occurrence of 'e')
INSTR2('TechOnTheNet.com', 'e', 1, 1)
Result: 2 (the first occurrence of 'e')
INSTR2('TechOnTheNet.com', 'e', 1, 2)
Result: 9 (the second occurrence of 'e')
INSTR2('TechOnTheNet.com', 'e', 1, 3)
Result: 11 (the third occurrence of 'e')
INSTR2('TechOnTheNet.com', 'e', -3, 2)
Result: 9
ORACLE/PLSQL: INSTR4 FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL INSTR4 function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL INSTR4 function returns the location of a substring in a string, using UCS4 code points.
SYNTAX
The syntax for the Oracle/PLSQL INSTR4 function is:
INSTR4( string, substring [, start_position [, nth_appearance ] ] )
Parameters or Arguments
string is the string to search. string can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. string can not be CLOB or NCLOB.
substring is the substring to search for in string. substring can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
start_position is the position in string where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the INSTR4 function counts back start_position number of characters from the end of string and then searches towards the beginning of string.
nth_appearance is the nth appearance of substring. This is optional. If omitted, it defaults to 1.
NOTE
- If substring is not found in string, then the INSTR4 function will return 0.
APPLIES TO
The INSTR4 function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle INSTR4 function examples and explore how to use the INSTR4 function in Oracle/PLSQL.
For example:
INSTR4('TechOnTheNet.com', 'e')
Result: 2 (the first occurrence of 'e')
INSTR4('TechOnTheNet.com', 'e', 1, 1)
Result: 2 (the first occurrence of 'e')
INSTR4('TechOnTheNet.com', 'e', 1, 2)
Result: 9 (the second occurrence of 'e')
INSTR4('TechOnTheNet.com', 'e', 1, 3)
Result: 11 (the third occurrence of 'e')
INSTR4('TechOnTheNet.com', 'e', -3, 2)
Result: 9
ORACLE/PLSQL: LENGTHB FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL LENGTHB function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL LENGTHB function returns the length of the specified string, using bytes instead of characters.
SYNTAX
The syntax for the Oracle/PLSQL LENGTHB function is:
LENGTHB( string1 )
Parameters or Arguments
string1 is the string to return the length for. string1 can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, or single-byte LOB.
NOTE
- If string1 is NULL, then the LENGTHB function will return NULL.
APPLIES TO
The LENGTHB function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle LENGTHB function examples and explore how to use the LENGTHB function in Oracle/PLSQL.
For example:
LENGTHB(NULL)
Result: NULL (single-byte character set)
LENGTHB('')
Result: NULL (single-byte character set)
LENGTHB(' ')
Result: 1 (single-byte character set)
LENGTHB('TechOnTheNet.com')
Result: 16 (single-byte character set)
LENGTHB('TechOnTheNet.com ')
Result: 17 (single-byte character set)
ORACLE/PLSQL: LENGTHC FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL LENGTHC function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL LENGTHC function returns the length of the specified string, using Unicode complete characters.
SYNTAX
The syntax for the Oracle/PLSQL LENGTHC function is:
LENGTHC( string1 )
Parameters or Arguments
string1 is the string to return the length for. string1 can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. string1 can not be CLOB or NCLOB.
NOTE
- If string1 is NULL, then the LENGTHC function will return NULL.
APPLIES TO
The LENGTHC function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle LENGTHC function examples and explore how to use the LENGTHC function in Oracle/PLSQL.
For example:
LENGTHC(NULL)
Result: NULL
LENGTHC('')
Result: NULL
LENGTHC(' ')
Result: 1
LENGTHC('TechOnTheNet.com')
Result: 16
LENGTHC('TechOnTheNet.com ')
Result: 17
ORACLE/PLSQL: LENGTH2 FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL LENGTH2 function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL LENGTH2 function returns the length of the specified string, using UCS2 code points.
SYNTAX
The syntax for the Oracle/PLSQL LENGTH2 function is:
LENGTH2( string1 )
Parameters or Arguments
string1 is the string to return the length for. string1 can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. string1 can not be CLOB or NCLOB.
NOTE
- If string1 is NULL, then the LENGTH2 function will return NULL.
APPLIES TO
The LENGTH2 function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle LENGTH2 function examples and explore how to use the LENGTH2 function in Oracle/PLSQL.
For example:
LENGTH2(NULL)
Result: NULL
LENGTH2('')
Result: NULL
LENGTH2(' ')
Result: 1
LENGTH2('TechOnTheNet.com')
Result: 16
LENGTH2('TechOnTheNet.com ')
Result: 17
ORACLE/PLSQL: LENGTH4 FUNCTION
This Oracle tutorial explains how to use the Oracle/PLSQL LENGTH4 function with syntax and examples.
DESCRIPTION
The Oracle/PLSQL LENGTH4 function returns the length of the specified string, using UCS4 code points.
SYNTAX
The syntax for the Oracle/PLSQL LENGTH4 function is:
LENGTH4( string1 )
Parameters or Arguments
string1 is the string to return the length for. string1 can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. string1 can not be CLOB or NCLOB.
NOTE
- If string1 is NULL, then the LENGTH4 function will return NULL.
APPLIES TO
The LENGTH4 function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXAMPLE
Let's look at some Oracle LENGTH4 function examples and explore how to use the LENGTH4 function in Oracle/PLSQL.
For example:
LENGTH4(NULL)
Result: NULL
LENGTH4('')
Result: NULL
LENGTH4(' ')
Result: 1
LENGTH4('TechOnTheNet.com')
Result: 16
LENGTH4('TechOnTheNet.com ')
Result: 17
No comments :
Post a Comment