Functions in SQL / Oracle:
Functions are programs that perform a certain task. A function has a name and may receive values. The function always returns one value of output. The are pre-defined functions in Oracle that can be used for commonly performed tasks.
The different types of pre-defined functions are listed on this slide:
The DUAL table is very useful when testing the outcome of single-row functions. This pre-defined table is accessible by all users of the database and is made up of one row and one column. It is used to see the result of a function once on the screen.
Character Functions
Given below are a list of the character functions that will be discussed.
Is used to convert a character string to uppercase.
In the example:
SELECT UPPER('hello') FROM DUAL;
Will display the word HELLO in uppercase.
The statement:
SELECT UPPER(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will display the job titles that begin with the letter M in uppercase.
SELECT UPPER('hello') FROM DUAL;
Will display the word HELLO in uppercase.
The statement:
SELECT UPPER(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will display the job titles that begin with the letter M in uppercase.
Is used to convert a character string to lowercase.
In the example:
SELECT LOWER('HELLO') FROM DUAL;
Will display the word HELLO in lowercase.
The statement:
SELECT LOWER(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will display the job titles that begin with the letter M in lowercase.
SELECT LOWER('HELLO') FROM DUAL;
Will display the word HELLO in lowercase.
The statement:
SELECT LOWER(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will display the job titles that begin with the letter M in lowercase.
Is used to convert the first letter of every new word to uppercase.
In the example:
SELECT INITCAP('hello world') FROM DUAL;
Will display the string 'hello world' in sentence case => Hello World
The statement:
SELECT INITCAP(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will display the job titles that begin with the letter M in Sentence Case.
SELECT INITCAP('hello world') FROM DUAL;
Will display the string 'hello world' in sentence case => Hello World
The statement:
SELECT INITCAP(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will display the job titles that begin with the letter M in Sentence Case.
Is used to display the length (number of characters) of a string.
In the example:
SELECT LENGTH('hello') FROM DUAL;
Will display the length of the word HELLO => 5.
The statement:
SELECT LENGTH(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will display the length of the job titles that begin with the letter M.
SELECT LENGTH('hello') FROM DUAL;
Will display the length of the word HELLO => 5.
The statement:
SELECT LENGTH(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will display the length of the job titles that begin with the letter M.
Is used to extract a portion of a string from another string.
In the example:
SELECT SUBSTR('HELLO',2,2) FROM DUAL;
Will extract 2 characters from the string hello, starting at the second character position -> EL
The statement:
SELECT UPPER(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will extract 10 characters from the job titles beginning with the letter M, starting from the third character position.
SELECT SUBSTR('HELLO',2,2) FROM DUAL;
Will extract 2 characters from the string hello, starting at the second character position -> EL
The statement:
SELECT UPPER(JOB_TITLE) FROM JOBS
WHERE JOB_TITLE LIKE 'M%';
Will extract 10 characters from the job titles beginning with the letter M, starting from the third character position.
Is used to combine two strings to create a resultant string.
In the example:
SELECT CONCAT('SQL','CLASS') FROM DUAL;
Will combine the two strings to create a single string.=> SQLCLASS
The statement:
SELECT CONCAT(FIRST_NAME,LAST_NAME) FROM EMPLOYEES
WHERE JOB_ID='AD_VP';
Will combine the first name and last name of employees who hold the job ID of AD_VP.
SELECT CONCAT('SQL','CLASS') FROM DUAL;
Will combine the two strings to create a single string.=> SQLCLASS
The statement:
SELECT CONCAT(FIRST_NAME,LAST_NAME) FROM EMPLOYEES
WHERE JOB_ID='AD_VP';
Will combine the first name and last name of employees who hold the job ID of AD_VP.
Numeric Functions Back to Top
Given below are a list of the numeric functions that will be discussed.
Syntax : ROUND(n,integer)
Returns a number 'n' (the first argument) rounded to a certain number of integer places (second parameter) to the right of the decimal point
If you don't specify a second parameter, the number will be rounded to zero decimal places.
In the example:
SELECT ROUND(27.5663,2) FROM DUAL;
The third digit after the decimal point is 6. As this number is greater than 5, the number rounded to 2 decimal places becomes: 27.57
In the example:
SELECT ROUND(27.5663) FROM DUAL;
The first digit after the decimal point is 5. As this number is equal to 5, the number rounded to 0 decimal places becomes: 28
In the example:
SELECT ROUND(27.5663,2) FROM DUAL;
The third digit after the decimal point is 6. As this number is greater than 5, the number rounded to 2 decimal places becomes: 27.57
In the example:
SELECT ROUND(27.5663) FROM DUAL;
The first digit after the decimal point is 5. As this number is equal to 5, the number rounded to 0 decimal places becomes: 28
Syntax:
TRUNC(n1,n2)
Returns n1 truncated to n2 decimal places.
If you don't specify a second parameter, the number will be truncated to zero decimal places.
In the example:
SELECT TRUNC(27.5663,2) FROM DUAL;
The number is truncated to 2 decimal places: 27.56
In the example:
SELECT TRUNC(27.5663) FROM DUAL;
The number is truncated to 0 decimal places becomes: 27
In the example:
SELECT TRUNC(27.5663,2) FROM DUAL;
The number is truncated to 2 decimal places: 27.56
In the example:
SELECT TRUNC(27.5663) FROM DUAL;
The number is truncated to 0 decimal places becomes: 27
Syntax:
ABS(n)
ABS(n)
Returns an absolute value of n. The absolute of a number (positive or negative) is the positive value of the number.
In the example:
SELECT ABS(-9) FROM DUAL;
The positive value of -9 is 9.
In the example:
SELECT ABS(9) FROM DUAL;
The positive value of 9 is 9.
In the example:
SELECT ABS(-9) FROM DUAL;
The positive value of -9 is 9.
In the example:
SELECT ABS(9) FROM DUAL;
The positive value of 9 is 9.
Syntax:
FLOOR(n)
Returns the largest integer equal or less than n.
FLOOR(n)
Returns the largest integer equal or less than n.
In the example:
SELECT FLOOR(38.4) FROM DUAL;
The largest integer value equal or less than 38.4, is 38
In the example:
SELECT FLOOR(-38.4) FROM DUAL;
The largest integer value equal or less than -38. is -39
SELECT FLOOR(38.4) FROM DUAL;
The largest integer value equal or less than 38.4, is 38
In the example:
SELECT FLOOR(-38.4) FROM DUAL;
The largest integer value equal or less than -38. is -39
Syntax:
CEIL(n)
Returns the smallest integer greater than or equal to n.
CEIL(n)
Returns the smallest integer greater than or equal to n.
In the example:
SELECT CEIL(38.4) FROM DUAL;
The largest integer value equal or greater than 38.4, is 39
In the example:
SELECT CEIL(-38.4) FROM DUAL;
The largest integer value equal or greater than -38 is -38
SELECT CEIL(38.4) FROM DUAL;
The largest integer value equal or greater than 38.4, is 39
In the example:
SELECT CEIL(-38.4) FROM DUAL;
The largest integer value equal or greater than -38 is -38
Syntax:
MOD(n1,n2)
Returns the remainder obtained by dividing n1 by n2. Returns n1 if n1 is zero.
MOD(n1,n2)
Returns the remainder obtained by dividing n1 by n2. Returns n1 if n1 is zero.
In the example:
SELECT MOD(21,4) FROM DUAL;
The remainder obtained after dividing 21 by 4 is 1.
In the example:
SELECT MOD(0,4) FROM DUAL;
When the first argument is 0, the result of the mod function is 0.
Note: To evaluate the result of a MOD function. Oracle uses the following expression:
MOD(m,n) = m - n * FLOOR(m/n)
A function similar to MOD is the REMAINDER function. However when evaluating the result of the REMAINDER function Oracle uses the formula:
MOD(m,n) = m - n * ROUND(m/n)
SELECT MOD(21,4) FROM DUAL;
The remainder obtained after dividing 21 by 4 is 1.
In the example:
SELECT MOD(0,4) FROM DUAL;
When the first argument is 0, the result of the mod function is 0.
Note: To evaluate the result of a MOD function. Oracle uses the following expression:
MOD(m,n) = m - n * FLOOR(m/n)
A function similar to MOD is the REMAINDER function. However when evaluating the result of the REMAINDER function Oracle uses the formula:
MOD(m,n) = m - n * ROUND(m/n)
Date Functions Back to Top
Given below are a list of the date functions that will be discussed.
Syntax:
ADD_MONTHS(date,n)
Returns a date, that is n months before (if n is negative) or after (if n is positive) the date.
ADD_MONTHS(date,n)
Returns a date, that is n months before (if n is negative) or after (if n is positive) the date.
In the example:
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;
You are adding three months to the current date. If the current date Aug 03, 2009, the output is 03-NOV-09.
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;
You are adding three months to the current date. If the current date Aug 03, 2009, the output is 03-NOV-09.
Syntax:
MONTHS_BETWEEN(date1, date2)
Returns a number representing the number of months between date1 and date2.
MONTHS_BETWEEN(date1, date2)
Returns a number representing the number of months between date1 and date2.
In the example:
SELECT FIRST_NAME, MONTHS_BETWEEN(SYSDATE,HIRE_DATE)
FROM EMPLOYEES
WHERE JOB_ID='AD_VP';
For employees whose JOB ID is AD_VP, the first name and the number of months that have elapsed since they were hired are being retrieved.
SELECT FIRST_NAME, MONTHS_BETWEEN(SYSDATE,HIRE_DATE)
FROM EMPLOYEES
WHERE JOB_ID='AD_VP';
For employees whose JOB ID is AD_VP, the first name and the number of months that have elapsed since they were hired are being retrieved.
Conversion Functions
Given below are a list of the conversion functions that will be discussed.
The TO_CHAR function:
Syntax:
TO_CHAR(str,'fmt')
Fmt -> can be a string created by date format element or numeric format elements.
The TO_CHAR function is used to convert a date type value or a numeric value into a character string. This is often used when you want to display a date in a format other than a DD-MON-YY format. For e.g. If you want to display the date : 05-25-10 in the format May 5th, 2010 you would need to convert it into a character string. Similarly if you want to display a numeric value like 50678.34 in the format $50,678.34 you will need to convert it into a character string.
| Format Element | Description | Example |
| YYYY | Full year in numbers | 2010 |
| YEAR | Year spelled out | Two thousand Nine |
| MM | Two digit value for month | 09 |
| MONTH | Full name of month | APRIL |
| MON | Three letter abbreviation of month | APR |
| DY | Three letter abbreviation of day of week | TUE |
| DAY | Full name of the day of the week | TUESDAY |
| DD | Numeric day of the month | 25 |
In the example:
SELECT TO_CHAR(SYSDATE,'DDth "of" MONTH, YYYY')
FROM DUAL;
We are displaying the current date in a format that will have a two digit day of month as an ordinal number, followed by the word of , followed by the Month (spelled out), followed by a comma, followed by a four digit year.
SELECT TO_CHAR(SYSDATE,'fmDDth "of" MONTH, YYYY')
FROM DUAL;
We are displaying the current date in a format similar to the previous example. In this case the difference is created by the characters fm that start the format string. The fm characters are used to remove unnecessary spaces in the output.
SELECT TO_CHAR(SYSDATE,'DDth "of" MONTH, YYYY')
FROM DUAL;
We are displaying the current date in a format that will have a two digit day of month as an ordinal number, followed by the word of , followed by the Month (spelled out), followed by a comma, followed by a four digit year.
SELECT TO_CHAR(SYSDATE,'fmDDth "of" MONTH, YYYY')
FROM DUAL;
We are displaying the current date in a format similar to the previous example. In this case the difference is created by the characters fm that start the format string. The fm characters are used to remove unnecessary spaces in the output.
| Format Element | Description | Format | Example |
| 9 | Represents a number | 9999.99 |
Number: 345.6
Output: 345.60
|
| 0 | Forces a zero to be displayed | 0000.00 |
Number: 345.6
Output: 0345.60
|
| $ | Places a floating dollar sign | $999.99 |
Number: 34.5
Output : $34.50
|
| . | Decimal Point | $99.9 |
Number : 38.99
Output : $38.99
|
| , | Thousands Indicator | $99,999.99 |
Number: 15042.5
Output: $15,042.50
|
TO_DATE(str,'fmt')
Converts a string representing a date that is in a non default date to a date value in the default date format.
Converts a string representing a date that is in a non default date to a date value in the default date format.
The TO_DATE function is used to convert a character string that represents a date into a date value.
In the example:
SELECT TO_DATE('3 of June, 2009','DD "of" Month, YYYY')
FROM DUAL;
The character string '3 of June, 2009' needs to be converted to a date as in 06-03-09. The second argument in the TO_DATE function tells Oracle how to read the first argument. It defines the format that the first argument is in. This tells Oracle how to read the first argument, to identify the individual date elements from the character string.
In the example:
SELECT TO_DATE('3 of June, 2009','DD "of" Month, YYYY')
FROM DUAL;
The character string '3 of June, 2009' needs to be converted to a date as in 06-03-09. The second argument in the TO_DATE function tells Oracle how to read the first argument. It defines the format that the first argument is in. This tells Oracle how to read the first argument, to identify the individual date elements from the character string.
TO_NUMBER(str,'fmt')
Converts a string representing a numeric value with format to a number without formatting. The TO_NUMBER function is used to convert a character string into a numeric value.
In the example:
SELECT TO_NUMBER('$45.67','$99.99')
FROM DUAL;
The first argument is a formatted numeric value with a dollar sign. The TO_NUMBER function is used to convert this string into a number as in 45.67. The second argument tells Oracle how to interpret the first argument. Using the second argument Oracle is able to identify the number without the formats.
SELECT TO_NUMBER('$45.67','$99.99')
FROM DUAL;
The first argument is a formatted numeric value with a dollar sign. The TO_NUMBER function is used to convert this string into a number as in 45.67. The second argument tells Oracle how to interpret the first argument. Using the second argument Oracle is able to identify the number without the formats.
NULL functions Back to Top
The following null functions will be discussed in this article.
Syntax:
NVL(value,default)
Substitutes the second argument when the first argument's value is null. The datetype of the second argument must match that of the first argument.
NVL(value,default)
Substitutes the second argument when the first argument's value is null. The datetype of the second argument must match that of the first argument.
In the example:
SELECT NVL(COMMISSION_PCT,0)
FROM EMPLOYEES
WHERE JOB_ID IN ('AD_VP','SA_MAN');
You are displaying the commission percent for all those employees whose JOB ID is either AD_VP or SA_MAN. For those employees whose COMMISSION_PCT value is NULL , a 0 will be substituted in the output.
SELECT NVL(COMMISSION_PCT,0)
FROM EMPLOYEES
WHERE JOB_ID IN ('AD_VP','SA_MAN');
You are displaying the commission percent for all those employees whose JOB ID is either AD_VP or SA_MAN. For those employees whose COMMISSION_PCT value is NULL , a 0 will be substituted in the output.
Syntax:
NVL2(value,default1,default2)
Substitutes the second argument when the first argument's value is not null. Substitutes the third argument when the first argument is null.
NVL2(value,default1,default2)
Substitutes the second argument when the first argument's value is not null. Substitutes the third argument when the first argument is null.
In the example:
SELECT NVL2(COMMISSION_PCT, 'COMMISSION','NO COMMISSION')
FROM EMPLOYEES
WHERE JOB_IN IN ('AD_VP','SA_MAN');
The COMMISSION_PCT column in the table takes either null values or non-null numeric values. The NVL2 function has substitute values for both the nulls and not-nulls. When the column's value is a null, the string 'NO COMMISSION' is displayed, when the value is a number, the string 'COMMISSION' is displayed.
SELECT NVL2(COMMISSION_PCT, 'COMMISSION','NO COMMISSION')
FROM EMPLOYEES
WHERE JOB_IN IN ('AD_VP','SA_MAN');
The COMMISSION_PCT column in the table takes either null values or non-null numeric values. The NVL2 function has substitute values for both the nulls and not-nulls. When the column's value is a null, the string 'NO COMMISSION' is displayed, when the value is a number, the string 'COMMISSION' is displayed.
NULLIF(expr1,expr2)
NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.
NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.
In the example:
SELECT LENGTH(FIRST_NAME), LENGTH(LAST_NAME),
NULLIF(LENGTH(FIRST_NAME), LENGTH(LAST_NAME))
FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN '30-JUN-97' AND '30-DEC-97';
The NULLIF function will return a null value when the length of the FIRST_NAME is equal to the length of the LAST_NAME.
SELECT LENGTH(FIRST_NAME), LENGTH(LAST_NAME),
NULLIF(LENGTH(FIRST_NAME), LENGTH(LAST_NAME))
FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN '30-JUN-97' AND '30-DEC-97';
The NULLIF function will return a null value when the length of the FIRST_NAME is equal to the length of the LAST_NAME.
I hope this will helps to you.
No comments:
Post a Comment