Single Row Functions in Oracle
There are two types of SQL functions available in oracle:
- Single row function
- Multiple row function
Single row function: It works on a single row and returns one result for each row.
Multiple row function: It works on multiple rows or a group of rows. It returns one result for each group.
Features of Single row function:
- Single row function take one or more parameters for each row and returns one result per row
- Single row function can be used in SELECT, WHERE, ORDER BY clause
- Data type of input parameters and output result may be different
- Single row function can be nested, i.e. we can call a single row function inside another single row function
Types of Single row function:
- Character Function
- Number Function
- Date Function
- Conversion Function
- Nested Function
- General Function
Character Functions:
Character functions take character as input parameters.
Types of Character Functions:
- Case manipulation function
- LOWER
- UPPER
- INITCAP
- Character manipulation function
- LENGTH
- CONCAT
- SUBSTR
- INSTR
- LPAD
- RPAD
- TRIM
- LTRIM
- RTRIM
- REPLACE
Case manipulation functions:
LOWER:
It converts all characters of a given string to lower case.
SELECT EMPLOYEE_ID,
FIRST_NAME,
LOWER(FIRST_NAME) LOWER_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
LOWER(JOB_ID) LOWER_JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND LOWER(JOB_ID) = 'pu_clerk'
ORDER BY LOWER(FIRST_NAME); UPPER:
It converts all characters of a given string to upper case.
SELECT EMPLOYEE_ID,
FIRST_NAME,
UPPER(FIRST_NAME) UPPER_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; SELECT EMPLOYEE_ID,
FIRST_NAME,
UPPER(FIRST_NAME) UPPER_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID,
UPPER('Employee - > '||UPPER(FIRST_NAME)||' works in department '||DEPARTMENT_ID) REMARKS
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND JOB_ID = UPPER('pu_clerk')
ORDER BY UPPER(FIRST_NAME); INITCAP:
It converts the first character of every word in a string to upper case and rest to lower case.
SELECT EMPLOYEE_ID,
FIRST_NAME,
INITCAP(FIRST_NAME) INITCAP_FIRST_NAME,
JOB_ID,
INITCAP(JOB_ID) INITCAP_JOB_ID,
DEPARTMENT_ID,
INITCAP('Employee - > '||UPPER(FIRST_NAME)||' works in department '||DEPARTMENT_ID) REMARKS
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND INITCAP(JOB_ID) = 'Pu_Clerk'
ORDER BY INITCAP(FIRST_NAME); Character manipulation functions:
LENGTH:
It returns the number of characters inside a string.
SELECT EMPLOYEE_ID,
FIRST_NAME,
LENGTH(FIRST_NAME) LENGTH_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID,
LENGTH('Employee - > '||UPPER(FIRST_NAME)||' works in department '||DEPARTMENT_ID) LENGTH_REMARKS
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY LENGTH(FIRST_NAME); SELECT EMPLOYEE_ID,
FIRST_NAME,
LENGTH(FIRST_NAME) LENGTH_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID,
LENGTH('Employee - > '||UPPER(FIRST_NAME)||' works in department '||DEPARTMENT_ID) LENGTH_REMARKS
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND LENGTH(FIRST_NAME) < 5
ORDER BY LENGTH(FIRST_NAME); CONCAT:
It appends two given string.
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
CONCAT(FIRST_NAME, LAST_NAME) FULL_NAME,
CONCAT(FIRST_NAME||' ', LAST_NAME) FULL_NAME_WITH_SPACE,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY CONCAT(FIRST_NAME, LAST_NAME); SUBSTR:
It returns the portion of a large string based on the positional parameter passed.
SELECT EMPLOYEE_ID,
FIRST_NAME,
SUBSTR(FIRST_NAME, 1, 3) FNAME,
LAST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; In the above example the SUBSTR function returns the first 3 letter of FIRST_NAME column starting from first character.
SELECT EMPLOYEE_ID,
FIRST_NAME,
SUBSTR(FIRST_NAME, 2, 3) FNAME,
LAST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; In the above example the SUBSTR function returns the first 3 letter of FIRST_NAME column starting from second character.
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
CONCAT(SUBSTR(FIRST_NAME, 1, 1),
SUBSTR(LAST_NAME, 1, 1)) NAME_INITIALS,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; In the above example we have used the Nested feature of single row function. We have called SUBSTR function inside CONCAT function.
INSTR:
It returns the numeric position of a small string inside a large string based on the search condition provided as parameters.
SELECT EMPLOYEE_ID,
FIRST_NAME,
INSTR(FIRST_NAME, 'e', 1, 1) FIRST_E_IN_FIRST_NAME,
INSTR(FIRST_NAME, 'e', 1, 2) SECOND_E_IN_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; In the above example, we have shown the first and second occurrences of letter e in FIRST_NAME column starting from position 1.
LPAD:
It padded a character value left-justified to a string up to the width specified in the parameter.
SELECT EMPLOYEE_ID,
FIRST_NAME,
LPAD(FIRST_NAME, 12, '*') LPAD_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30;
In the above example we have left padded asterisk up to 12 characters.
RPAD:
It padded a character value right-justified to a string up to the width specified in the parameter.
SELECT EMPLOYEE_ID,
FIRST_NAME,
RPAD(FIRST_NAME, 12, '*') RPAD_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; In the above example we have right padded asterisk up to 12 characters.
TRIM:
It removes a portion of a string from the source string.
SELECT EMPLOYEE_ID,
FIRST_NAME,
TRIM('S' FROM FIRST_NAME) TRIM_FIRST_NAME,
TRIM(SUBSTR(FIRST_NAME, 1, 1) FROM FIRST_NAME) TRIM_FIRST_NAME_SUBSTR,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; In the above example, TRIM_FIRST_NAME column removes S from FIRST_NAME where it starts with S. In the TRIM_FIRST_NAME_SUBSTR column removes the first character of every FIRST_NAME.
LTRIM:
LTRIM is used for removing a substring or a character from left most side of original string.
SELECT EMPLOYEE_ID,
FIRST_NAME,
LTRIM(FIRST_NAME, 'S') LTRIM_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; In the above example, LTRIM has removed the starting S from EMPLOYEE_ID 116 and 117.
SELECT EMPLOYEE_ID,
FIRST_NAME,
LTRIM(FIRST_NAME, 'Si') LTRIM_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; In the above example, LTRIM has removed Si from FIRST_NAME Sigal. But from FIRST_NAME Shelli, it has removed only S as there is no Si present at the beginning.
RTRIM:
RTRIM is used for removing a substring or a character from right most side of original string.
SELECT EMPLOYEE_ID,
FIRST_NAME,
RTRIM(FIRST_NAME, 'n') RTRIM_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; In the above example RTRIM has removed the right most character n from EMPLOYEE_ID 208, 114, 119.
SELECT EMPLOYEE_ID,
FIRST_NAME,
RTRIM(FIRST_NAME, 'en') RTRIM_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; In the above example, RTRIM has removed en from EMPLOYEE_ID from 114 and 119. But it has removed only n from EMPLOYEE_ID 208 as there is no en at the right most side of FIRST_NAME.
REPLACE:
REPLACE function is used to replace a character or sub-string with some other character or sub-string in the main string.
SELECT EMPLOYEE_ID,
FIRST_NAME,
REPLACE(FIRST_NAME, 'S', 'K') REPLACE_FIRST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; In the above example, RELACE has replaced S in the FIRST_NAME of EMPLOYEE_ID 116 and 117 with K.
Number Functions:
Number function takes number as input parameters.
- ROUND
- TRUNC
- MOD
- CEIL
- FLOOR
ROUND:
This function is rounded up one value to n decimal places. n can be a positive, 0 or negative.
SELECT ROUND(12.5648, 3) ROUND_THREE,
ROUND(12.5648, 2) ROUND_TWO,
ROUND(12.5648, 1) ROUND_ONE,
ROUND(12.5648, 0) ROUND_ZERO,
ROUND(12.5648, -1) ROUND_NEGATIVE_ONE,
ROUND(12.5648, -2) ROUND_NEGATIVE_TWO
FROM DUAL; TRUNC:
This function is truncate one value to n decimal places. n can be a positive, 0 or negative.
SELECT TRUNC(12.5648, 3) TRUNC_THREE,
TRUNC(12.5648, 2) TRUNC_TWO,
TRUNC(12.5648, 1) TRUNC_ONE,
TRUNC(12.5648, 0) TRUNC_ZERO,
TRUNC(12.5648, -1) TRUNC_NEGATIVE_ONE,
TRUNC(12.5648, -2) TRUNC_NEGATIVE_TWO
FROM DUAL; MOD:
MOD returns the reminder of division of two numbers.
SELECT MOD(12, 5) MOD_12_5 FROM DUAL;
SELECT MOD(12, 6) MOD_12_6 FROM DUAL;
CEIL:
CEIL function returns the next smallest integer which is greater than the decimal value passed as parameter.
SELECT CEIL(12.15) CEIL_TEST FROM DUAL;
If the passed parameter is itself an integer then CEIL returns the parameter value.
SELECT CEIL(12) CEIL_TEST FROM DUAL;
FLOOR:
FLOOR function returns the next largest integer which is less than the decimal value passed as parameter.
SELECT FLOOR(12.96) FLOOR_TEST FROM DUAL;
If the passed parameter is itself an integer then FLOOR returns the parameter value.
SELECT FLOOR(12) FLOOR_TEST FROM DUAL;
Date Functions:
Date function used date as parameter and do the manipulation.
- MONTHS_BETWEEN
- ADD_MONTHS
- LAST_DAY
- NEXT_DAY
- ROUND
- TRUNC
MONTHS_BETWEEN:
MONTHS_BETWEEN takes two dates as parameter and returns the month difference between them.
SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
HIRE_DATE,
ROUND(MONTHS_BETWEEN(SYSDATE, HIRE_DATE), 0) MONTHS_WORKED,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE DESC; In the above example, data is sorted with descending order of HIRE_DATE. So that new joiners are sorted in the above in the list.
ADD_MONTHS:
ADD_MONTHS add the number of months passed as parameter to a given date and returns the future date.
SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
HIRE_DATE,
ADD_MONTHS(HIRE_DATE, 30*12) RETIRED_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE; In the above example we have added 30 years with HIRE_DATE to get the RETIRED_DATE.
LAST_DAY:
LAST_DAY function returns the last day of the month for the date passed as parameter.
SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
HIRE_DATE,
LAST_DAY(HIRE_DATE) LAST_DAY_HIRED_MONTH,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE; The above example LAST_DAY function takes HIRE_DATE as parameter and returns the last day of hired month.
NEXT_DAY:
NEXT_DAY takes a date as input parameter along with a day say MONDAY, and returns the next MONDAY which comes after the parameter date.
SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
HIRE_DATE,
NEXT_DAY(HIRE_DATE, 'MONDAY') NEXT_MONDAY_AFTER_HIRED,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE; ROUND:
ROUND function takes a date as input parameter along with one of below inputs and rounded it to a new date:
- YEAR
- MONTH
- WEEK
- DAY
YEAR:
SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
HIRE_DATE,
ROUND(HIRE_DATE, 'YEAR') ROUND_YEAR_HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE; In the above example we have used ROUND function on HIRE_DATE and rounded it with YEAR. The dates which are less than or equal to 30-JUN are rounded to 01-JAN of same year. The dates which above 30-JUN are rounded to next year 01-JAN.
MONTH:
SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
HIRE_DATE,
ROUND(HIRE_DATE, 'MONTH') ROUND_MONTH_HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE; In the above example we have used ROUND function to rounded the HIRE_DATE with MONTH. The HIRE_DATE days which are less than or equals to 15 are rounded to 1st day of hired month. And which are more than 15 rounded to 1st day of next to hired month.
WEEK:
SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'DAY') HIRE_DAY,
TO_CHAR(TO_DATE('01-JAN-'||TO_CHAR(HIRE_DATE, 'RRRR')), 'DAY') FIRST_DAY_HIRE_YEAR,
ROUND(HIRE_DATE, 'WW') ROUND_WEEK_HIRE_DATE,
TO_CHAR(ROUND(HIRE_DATE, 'WW'), 'DAY') ROUND_WEEK_HIRE_DAY,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE; In the above example, we have rounded the HIRE_DATE based on week. The rule for WEEK round is that, oracle will first consider the day of 1st January of hire year. Then it will round the HIRE_DATE to nearest the same day of 1st January of HIRE Year.
For EMPLOYEE_ID 114 HIRE_DATE is 07-Dec-2002. 1st January of 2002 is TUESDAY. So when we round the HIRE_DATE based on week oracle will calculate the nearest TUESDAY and rounded it to that date. Here next nearest TUESDAY after 07-Dec-2002 is 10-Dec-2002.
DAY:
SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'DAY') HIRE_DAY,
ROUND(HIRE_DATE, 'DAY') ROUND_DAY_HIRE_DATE,
TO_CHAR(ROUND(HIRE_DATE, 'DAY'), 'DAY') ROUND_HIRE_DAY,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE; In the above example ROUND function round the HIRE_DATE to next nearest SUNDAY.
TRUNC:
TRUNC function takes a date as input parameter along with one of below inputs and truncated it to a new date:
YEAR:
SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
HIRE_DATE,
TRUNC(HIRE_DATE, 'YEAR') TRUNC_YEAR_HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE; In the above example we have used TRUNC function to truncate the YEAR. The result of YEAR truncation has changed the HIRE_DATE column to 1st January of respective hire year.
MONTH:
SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
HIRE_DATE,
TRUNC(HIRE_DATE, 'MONTH') TRUNC_MONTH_HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE; In the above example we have used TRUNC function to truncate the MONTH. The result of MONTH truncation has changed the HIRE_DATE column to last day of hire month.
WEEK:
SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'DAY') HIRE_DAY,
TO_CHAR(TO_DATE('01-JAN-'||TO_CHAR(HIRE_DATE, 'RRRR')), 'DAY') FIRST_DAY_HIRE_YEAR,
TRUNC(HIRE_DATE, 'WW') TRUNC_WEEK_HIRE_DATE,
TO_CHAR(TRUNC(HIRE_DATE, 'WW'), 'DAY') TRUNC_WEEK_HIRE_DAY,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE; In the above example, please check that, FIRST_DAY_HIRE_YEAR column represents the DAY of 1st January of each hire year. Now TRUNC function truncated the HIRE_DATE column with WEEK. It converts the HIRE_DATE column to previous nearest date whose DAY is equivalent to FIRST_DAY_HIRE_YEAR. In the first row HIRE_DATE is 07-Dec-2002 and its a Saturday. 1st January of 2002 was a Tuesday. So nearest Tuesday before 07-Dec-2002 was 03-Dec-2002.
DAY:
SELECT EMPLOYEE_ID,
FIRST_NAME,
JOB_ID,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'DAY') HIRE_DAY,
TRUNC(HIRE_DATE, 'DAY') TRUNC_DAY_HIRE_DATE,
TO_CHAR(TRUNC(HIRE_DATE, 'DAY'), 'DAY') TRUNC_HIRE_DAY,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE; In the above example we have truncated the HIRE_DATE column with DAY. The result is that its truncated each HIRE_DATE to previous nearest Sunday. In the first row the HIRE_DATE is 07-Dec-2002 and its a Saturday. So previous nearest Sunday is 01-Dec-2002.
Conversion Functions:
Oracle provides built-in functions to convert a Character to Number or Date type. Conversion can be happened in two ways:
- Implicit Conversion
- Explicit Conversion
Implicit Conversion:
If we put a character value in a number or date field and oracle is able to convert that character to number or date and vice versa without any failure then its called implicit conversion.
Explicit Conversion:
If we use oracle built-in function to convert a character to number or date or vice-versa then its called explicit conversion.
Character to Number Conversion:
TO_NUMBER:
SELECT TO_NUMBER('999') TO_NUMBER_CONVERION FROM DUAL; Character to Date Conversion:
TO_DATE:
Default date format is DD-MON-RR.
SELECT TO_DATE('05-12-2022', 'DD-MM-RRRR') TO_DATE_CONVERION FROM DUAL; In the above example we have used the format as DD-MM-RRRR as the character value which we are converting to date is in that format.
But if we convert a character which is already in default format then we don't need to use any format inside TO_DATE function.
SELECT TO_DATE('05-DEC-2022') TO_DATE_CONVERION FROM DUAL; Same is not true for any other format and will raise an error.
SELECT TO_DATE('05-12-2022') TO_DATE_CONVERION FROM DUAL; TO_CHAR:
Number to Character Conversion:
9 Format:
SELECT TO_CHAR('1234.5', 'FM99999') NUMBER_TO_CHAR_CONVERION FROM DUAL; SELECT TO_CHAR('1234.2', 'FM99999') NUMBER_TO_CHAR_CONVERION FROM DUAL; The above format rounding the decimal value.
SELECT TO_CHAR('1234.5', 'FM99999.9') NUMBER_TO_CHAR_CONVERION FROM DUAL; If the number of 9 in the format is less than the number of digits of a given value then oracle returns error.
SELECT TO_CHAR('12346', 'FM$9999') NUMBER_TO_CHAR_CONVERION FROM DUAL; Thousand and Decimal Operator:
SELECT TO_CHAR('1234.2', 'FM9G999G999D00') NUMBER_TO_CHAR_CONVERION FROM DUAL; Leading 0 Format:
SELECT TRIM(TO_CHAR('12346', 'FM099999')) NUMBER_TO_CHAR_CONVERION FROM DUAL; $ Currency Format:
SELECT TRIM(TO_CHAR('12346', 'FM$99999')) NUMBER_TO_CHAR_CONVERION FROM DUAL; SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
JOB_ID,
TO_CHAR(SALARY, 'FM$99,99,999') SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
JOB_ID,
TO_CHAR(SALARY, 'FM$99G99G999') SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; Date to Character Conversion:
TO_CHAR:
YYYY -> Full Year Number Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'YYYY') HIRE_YEAR,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; YEAR -> Full Year Character Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'YEAR') HIRE_YEAR,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; MM -> Month in Two Digit Number Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'MM') HIRE_MONTH,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; MON -> Month in 3 Letter Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'MON') HIRE_MONTH,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; MONTH -> Month in Complete Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'MONTH') HIRE_MONTH,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; DY-> Day in 3 Letter Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'DY') HIRE_DAY,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; DAY-> Day in Complete Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'DAY') HIRE_DAY,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; D -> Day of the Week Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'D') HIRE_DAY_OF_THE_WEEK,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; DD -> Day of the Month Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'DD') HIRE_DAY_OF_THE_MONTH,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; DDD-> Day of the Year Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'DDD') HIRE_DAY_OF_THE_YEAR,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; W-> Week of the Month Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'W') HIRE_WEEK_OF_THE_MONTH,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; WW-> Week of the Year Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'WW') HIRE_WEEK_OF_THE_YEAR,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; Q-> Quarter of the Year Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'Q') HIRE_QUARTER_OF_THE_YEAR,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; Date with Hour, Minute and Second Format:
SELECT TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') CURRENT_DATE_TIME FROM DUAL;
Date with Customized Format:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'FMDDTH MONTH, YEAR') HIRE_DATE_CUSTOMIZED_FORMAT,
JOB_ID,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; SELECT TO_CHAR(SYSDATE, 'FMDDSPTH "OF" MONTH, YEAR HH24:MI:SS AM') CURRENT_DATE_TIME FROM DUAL; FROM DUAL;
Difference between RR and YY Date Formats:
Setup:
CREATE TABLE RR_YY_TAB (ID NUMBER, RR_DATE DATE, YY_DATE DATE );
INSERT INTO RR_YY_TAB(ID, RR_DATE, YY_DATE) VALUES(1, '10-OCT-1995', '10-OCT-1995'); INSERT INTO RR_YY_TAB(ID, RR_DATE, YY_DATE) VALUES(2, '10-OCT-0095', '10-OCT-0095'); INSERT INTO RR_YY_TAB(ID, RR_DATE, YY_DATE) VALUES(3, '10-OCT-22', '10-OCT-22');
SELECT * FROM RR_YY_TAB;
YYYY Format:
SELECT ID,
TO_CHAR(RR_DATE, 'DD-MON-YYYY') RR_DATE,
TO_CHAR(YY_DATE, 'DD-MON-YYYY') YY_DATE
FROM RR_YY_TAB; RRRR Format:
SELECT ID,
TO_CHAR(RR_DATE, 'DD-MON-RRRR') RR_DATE,
TO_CHAR(YY_DATE, 'DD-MON-RRRR') YY_DATE
FROM RR_YY_TAB; So we can see that when we are using complete year format then there is no difference between YYYY and RRRR format.
INSERT INTO RR_YY_TAB(ID, RR_DATE, YY_DATE) VALUES(4, TO_DATE('10-OCT-22', 'DD-MON-RRRR'), TO_DATE('10-OCT-22', 'DD-MON-YYYY')); SELECT ID,
TO_CHAR(RR_DATE, 'DD-MON-YYYY') RR_DATE,
TO_CHAR(YY_DATE, 'DD-MON-YYYY') YY_DATE
FROM RR_YY_TAB
WHERE ID = 4; INSERT INTO RR_YY_TAB(ID, RR_DATE, YY_DATE) VALUES(5, TO_DATE('10-OCT-95', 'DD-MON-RRRR'), TO_DATE('10-OCT-95', 'DD-MON-YYYY')); SELECT ID,
TO_CHAR(RR_DATE, 'DD-MON-YYYY') RR_DATE,
TO_CHAR(YY_DATE, 'DD-MON-YYYY') YY_DATE
FROM RR_YY_TAB
WHERE ID = 5; INSERT INTO RR_YY_TAB(ID, RR_DATE, YY_DATE) VALUES(6, TO_DATE('10-OCT-95', 'DD-MON-RR'), TO_DATE('10-OCT-95', 'DD-MON-YY')); SELECT ID,
TO_CHAR(RR_DATE, 'DD-MON-YYYY') RR_DATE,
TO_CHAR(YY_DATE, 'DD-MON-YYYY') YY_DATE
FROM RR_YY_TAB
WHERE ID = 6; INSERT INTO RR_YY_TAB(ID, RR_DATE, YY_DATE) VALUES(7, TO_DATE('10-OCT-25', 'DD-MON-RR'), TO_DATE('10-OCT-25', 'DD-MON-YY')); SELECT ID,
TO_CHAR(RR_DATE, 'DD-MON-YYYY') RR_DATE,
TO_CHAR(YY_DATE, 'DD-MON-YYYY') YY_DATE
FROM RR_YY_TAB
WHERE ID = 7; In the above example, we can see that RR format has always inserted the data correctly.
| 2 Digits of Current Year | 2 Digits of given Year | Result of RR Format | Result of YY Format |
|---|---|---|---|
| 0 – 49 | 0 – 49 | The return date is in current century | The return date is in current century |
| 0 – 49 | 50 – 99 | The return date is in previous century | The return date is in current century |
| 50 – 99 | 0 – 49 | The return date is in next century | The return date is in current century |
| 50 – 99 | 50 – 99 | The return date is in current century | The return date is in current century |
Example:
| Current Year | Given Date with 2 digit Year | Result of RR Format | Result of YY Format |
|---|---|---|---|
| 2022 | 10-OCT-25 | 10-OCT-2025 | 10-OCT-2025 |
| 2022 | 10-OCT-95 | 10-OCT-1995 | 10-OCT-2095 |
| 1990 | 10-OCT-25 | 10-OCT-2025 | 10-OCT-1925 |
| 1990 | 10-OCT-95 | 10-OCT-1995 | 10-OCT-1995 |
Nested Functions:
Function Nesting is simply means that we can call one function inside another function.
Single row function can be nested to any level. The navigation will be started from most inside level. The inside function result can be used as an input to next outer function.
Character Nesting Functions:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
JOB_ID,
SALARY,
DEPARTMENT_ID,
NVL(TO_CHAR(MANAGER_ID), 'NO MANAGER') MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90; Number Nesting Function:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
JOB_ID,
CONCAT('SALARY IS -> ', TO_CHAR(SALARY, 'FM$999999')) SALARY,
DEPARTMENT_ID,
MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; Date Nesting Function:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
TO_CHAR(LAST_DAY(HIRE_DATE), 'FMDDSPTH "OF" MONTH, YEAR') LAST_DAY_HIRE_MONTH,
JOB_ID,
SALARY,
DEPARTMENT_ID,
MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90; General Functions:
General functions can be used with any data types.
- NVL
- NVL2
- NULLIF
- COALESCE
- CONDITIONAL FUNCTIONS
- DECODE
- CASE
NVL:
NVL function is used to replace a NULL value with some other value.
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
NVL(TO_CHAR(COMMISSION_PCT), 'NO COMMISSION') COMMISSION_PCT_NVL,
DEPARTMENT_ID,
MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
HIRE_DATE,
NVL(HIRE_DATE, SYSDATE) HIRE_DATE_NVL,
SALARY,
DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30; NVL2:
The NVL2 function takes 3 parameters as input. If the first parameter is not null then it returns second parameter. If the first parameter is null then it returns third parameter.
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
SALARY * COMMISSION_PCT COMMISSION_AMT,
NVL2(COMMISSION_PCT, (SALARY + SALARY*COMMISSION_PCT), SALARY) TOTAL_SALARY,
DEPARTMENT_ID,
MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; NULLIF:
NULLIF function takes two parameters as input. If both the parameters are equal then it returns NULL else returns the first parameter.
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
NULLIF(LENGTH(FIRST_NAME), LENGTH(LAST_NAME)) NAME_LENGTH_DIFF,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100; COALESCE:
COALESCE function takes n number of parameter as input. It returns the first not null parameter in the list. It returns the nth parameter if all other parameters are null.
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
COALESCE(COMMISSION_PCT*SALARY, SALARY*.05, 2000) COMMISSION_AMOUNT,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30; Conditional Functions:
These functions are follows IF-THEN-ELSE conditions.
DECODE:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
JOB_ID,
DECODE(JOB_ID, 'IT_PROG', 'IT PROGRAMMAR',
'AD_VP', 'VICE PRESIDENT',
'PRESIDENT') JOB_DESCRIPTION,
SALARY,
COMMISSION_PCT,
DEPARTMENT_ID,
MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (60, 90); CASE:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
JOB_ID,
CASE WHEN JOB_ID = 'IT_PROG' THEN 'IT PROGRAMMAR'
WHEN JOB_ID = 'AD_VP' THEN 'VICE PRESIDENT'
ELSE 'PRESIDENT' END JOB_DESCRIPTION,
SALARY,
COMMISSION_PCT,
DEPARTMENT_ID,
MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (60, 90); SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
HIRE_DATE,
JOB_ID,
SALARY,
CASE WHEN SALARY > 15000 THEN 'HIGH'
WHEN SALARY BETWEEN 6000 AND 10000 THEN 'MEDIUM'
ELSE 'LOW' END SALARY_TYPE,
COMMISSION_PCT,
DEPARTMENT_ID,
MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (60, 90); RELATED TOPICS: