function

COVAR_POP Function in Oracle SQL – PLSQL

November 27, 2012 by techhoneyadmin

The COVAR_POP Function in Oracle SQL / PLSQL is used to calculate the population covariance of a set of numbers in pair.
Syntax for the using the COVAR_POP Function in Oracle SQL / PLSQL is:

SELECT COVAR_POP(expression_1, expression_2)
FROM table_name;

expression_1 and expression_2 are numeric expressions
The COVAR_POP function in Oracle SQL / PLSQL will eliminate all the pairs where expression_1 and expression_2 are having NULL values.

Examples:

Using COVAR_POP Function in Oracle SQL / PLSQL SELECT statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10
107	Emp G	12000	Sales	10
108	Emp H	12000	Support

If we write our COVAR_POP Function query as:

SELECT COVAR_POP(salary, commission)
FROM employee;

We will get ‘-6000’ as output

CORR Function in Oracle SQL – PLSQL

November 27, 2012 by techhoneyadmin

The CORR Function in Oracle SQL / PLSQL is used to calculate the coefficient of correlation in a set of numbers.

Syntax for the using the CORR Function in Oracle SQL / PLSQL is:

SELECT CORR(n,m)[OVER analytic_clause]
FROM table_name;

n and m are numbers used to calculate the coefficient of correlation.
The CORR function in Oracle SQL / PLSQL requires at least 2 records or rows in a table.

Example:

Using CORR Function in Oracle SQL / PLSQL SELECT statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10
107	Emp G	12000	Sales	10
108	Emp H	12000	Support

If we write our CORR Function query as:

SELECT CORR(salary, commission)
FROM employee;

We will get ‘-0.174844651545059’ as output.

BIN_TO_NUM Function in Oracle SQL – PLSQL

November 27, 2012 by techhoneyadmin

The BIN_TO_NUM Function in Oracle SQL / PLSQL is used to convert the bit vector to a number type.

Syntax for the using the BIN_TO_NUM Function in Oracle SQL / PLSQL is:

SELECT BIT_TO_NUM(expression1,expression2, . .expressionN)
FROM table_name;

expression1, expression2, . ., expressionN must be either 0 or 1 and they represent bits in a bit vector.

Examples:
Using BIN_TO_NUM Function in Oracle SQL / PLSQL SELECT statement:

SELECT  BIN_TO_NUM(1)
FROM dual;

Will return ‘1’

SELECT  BIN_TO_NUM(1,1)
FROM dual;

Will return ‘3’

SELECT  BIN_TO_NUM(1,1,1)
FROM dual;

Will return ‘7’

SELECT  BIN_TO_NUM(1,1,1,0)
FROM dual;

Will return ‘14’

VSIZE Function in Oracle SQL – PLSQL

November 26, 2012 by techhoneyadmin

The VSIZE Function in Oracle SQL / PLSQL is used to get the number of bytes in the internal representation of an expression

Syntax for the using the VSIZE Function in Oracle SQL / PLSQL is:

SELECT VSIZE(expression)
FROM table_name;
expression is the string to be evaluated.

Examples:

Using VSIZE Function in Oracle SQL / PLSQL SELECT statement:

SELECT VSIZE('Tech Honey')
FROM dual;

Will return ‘10’

SELECT VSIZE('Tech')
FROM dual;

Will return ‘4’

SELECT VSIZE('Tech Honey is a good website')
FROM dual;

Will return ‘28’

Oracle PL/SQL TRIM Function

November 26, 2012 by techhoneyadmin

SQL TRIM Function is used to remove the specified characters from left and right sides of a string. Oracle TRIM Function returns the remaining string after removing the specified characters from left and right side of a string. PLSQL TRIM Function syntax with SQL TRIM String and SQL TRIM WHITESPACE examples are mentioned below.

Oracle TRIM Function Syntax

SELECT TRIM([trailing | leading | both [trim characters]] string_1)
FROM table_name;

In the above Oracle TRIM Function Syntax:-

string_1 is the string to be trimmed
‘Leading’ will remove the trim_characters from start_position in string_1.
‘Trailing’ will remove the trim_characters from the end of the string_1.
‘Both’ will remove the trim_characters from beginning and end of string_1
If we omit ‘leading’, ‘trailing’ or ‘both’ from the TRIM function then the TRIM function trims the string_1 from beginning and end.
If no trim_charachter is mentioned then TRIM function removes the white spaces from string_1

Oracle TRIM Function – Use in SQL TRIM String Example

Oracle TRIM Function can be used with the SQL SELECT Statement to TRIM a string.

For example, the below SQL TRIM String query returns the string after stripping the white spaces from left and right sides.

SELECT TRIM(' Tech Honey ')"SQL TRIM String"
FROM dual;

The above SQL TRIM String example returns ‘Tech Honey’

Note: We have aliased TRIM(‘ Tech Honey ‘) as SQL TRIM String.

Oracle TRIM Function – SQL TRIM WHITESPACE Example

Oracle TRIM Function can be used in SQL TRIM WHITESPACE query.

For example, the SQL TRIM WHITESPACE query below returns the string after removing the whitespaces from left and right side.

SELECT TRIM(' ' from ' Tech Honey ') "SQL TRIM WHITESPACE"
FROM dual;

The above SQL TRIM WHITESPACE query returns ‘Tech Honey’

Note: We have aliased TRIM(‘ ‘ from ‘ Tech Honey ‘) as SQL TRIM WHITESPACE.

Oracle TRIM Function – Removing LEADING Characters Example

Oracle TRIM Function can be used to remove any leading character from a string.

For example, the below PLSQL TRIM Function query returns the string after removing all the leading 1s from the string.

SELECT TRIM(LEADING 1 FROM '1111Tech Honey11111') "PLSQL TRIM Function"
FROM dual;

The above PLSQL TRIM Function query returns ‘Tech Honey11111’

Note we have aliased TRIM(LEADING 1 FROM ‘1111Tech Honey11111’) as PLSQL TRIM Function.

Oracle TRIM Function – Removing TRAILING Characters Example

PLSQL TRIM Function can be used to remove any trailing character from a string.

For example, the below TRIM Function query returns the string after removing all the trailing 1s from the string.

SELECT TRIM(TRAILING 1 FROM '1111Tech Honey11111') "PLSQL TRIM Function"
FROM dual;

The above PLSQL TRIM Function query returns ‘1111Tech Honey’.

Note: We have aliased TRIM(TRAILING 1 FROM ‘1111Tech Honey11111’) as PLSQL TRIM Function.

Oracle TRIM Function – Removing LEADING and TRAILING Characters Example

Oracle TRIM Function can be used to remove both the LEADING and TRAILING characters from a string.

For example, the TRIM Function query returns the string after removing leading and trailing 1s from the string.

SELECT TRIM(BOTH 1 FROM '1111Tech Honey11111') "Oracle TRIM Function"
FROM dual;

The above TRIM Function query returns ‘Tech Honey’.

Note: We have aliased TRIM(BOTH 1 FROM ‘1111Tech Honey11111’) as Oracle TRIM Function.