The options of single row features are:
- Act on every row returned within the question.
- Perform calculations on knowledge.
- Modify the information gadgets.
- Manipulate the output for teams of rows.
- Format numbers and dates.
- Converts column knowledge sorts.
- Returns one end result per row.
- Used in SELECT, WHERE and ORDER BY clauses.
- Single row features will be nested.
The single row features are categorized into
- Character Functions: Character features settle for character inputs and may return both character or quantity values as output.
- Number Functions: Number features accepts numeric inputs and returns solely numeric values as output.
- Date Functions: Date features function on date knowledge kind and returns a date worth or numeric worth.
- Conversions Functions: Converts from one knowledge kind to a different knowledge kind.
- General Functions
Let see every perform with an instance:
Table of Contents
Character Functions Example
1. LOWER
The Lower perform converts the character values into lowercase letters.
SELECT decrease('ORACLE') FROM DUAL;
2. UPPER
The Upper perform converts the character values into uppercase letters.
SELECT higher('oracle') FROM DUAL;
3. INITCAP
The Initcap perform coverts the primary character of every phrase into uppercase and the remaining characters into lowercase.
SELECT initcap('LEARN ORACLE') FROM DUAL;
4. CONCAT
The Concat perform coverts the primary string with the second string.
SELECT concat('Oracle',' Backup) FROM DUAL;
5. SUBSTR
The Substr perform returns specified characters from character worth beginning at place m and n characters lengthy. If you omit n, all characters ranging from place m to the top are returned.
Syntax: substr(string [,m,n])
SELECT substr('ORACLE DATA RECOVERY',8,4) FROM DUAL;
SELECT substr('ORACLE DATA PUMP',8) FROM DUAL;
You can specify m worth as unfavourable. In this case the depend begins from the top of the string.
SELECT substr('ORACLE BACKUP',-6) FROM DUAL;
6. LENGTH
The Length perform is used to search out the variety of characters in a string.
SELECT size('Oracle Data Guard') FROM DUAL;
7. INSTR
The Instr perform is used to search out the place of a string in one other string. Optionally you possibly can present place m to begin looking for the string and the incidence n of the string. By default m and n are 1 which implies to begin the search firstly of the search and the primary incidence.
Syntax: instr('Main String', 'substring', [m], [n])
SELECT instr('oralce apps','app') FROM DUAL;
SELECT instr('oralce apps is a superb utility','app',1,2) FROM DUAL;
8. LPAD
The Lpad perform pads the character worth right-justified to a complete width of n character positions.
Syntax: lpad(column, n, 'string');
SELECT lpad('100',5,'x') FROM DUAL;
9. RPAD
The Rpad perform pads the character worth left-justified to a complete width of n character positions.
Syntax: rpad(column, n, 'string');
SELECT rpad('100',5,'x') FROM DUAL;
10. TRIM
The Trim perform removes the main or trailing or each the characters from a string.
Syntax: trim(main|trailing|each, trim_char from trim_source)
SELECT trim('O' FROM 'ORACLE') FROM DUAL;
11. REPLACE
The Replace perform is used to exchange a personality with one other character in a string.
Syntax: substitute(column, old_char,new_char)
SELECT substitute('ORACLE DATA BACKUP', 'DATA','DATABASE') FROM DUAL;
Number Functions Example
1. ROUND
The Round perform rounds the worth to the n decimal values. If n shouldn’t be specified, there received’t be any decimal locations. If n is unfavourable, numbers to the left of the decimal level are rounded.
Syntax: spherical(quantity,n)
SELECT spherical(123.67,1) FROM DUAL;
SELECT spherical(123.67) FROM DUAL;
SELECT spherical(123.67,-1) FROM DUAL;
2. TRUNC
The Trunc perform truncates the worth to the n decimal locations. If n is omitted, then n defaults to zero.
Syntax: trunc(quantity,n)
SELECT trunc(123.67,1) FROM DUAL;
SELECT trunc(123.67) FROM DUAL;
3. MOD
The Mod perform returns the rest of m divided by n.
Syntax: mod(m,n)
SELECT mod(10,5) FROM DUAL;
Date Functions Example
1. SYSDATE
The Sysdate perform returns the present oracle database server date and time.
SELECT sysdate FROM DUAL;
2. Arithmetic with Dates
You can add or subtract the variety of days or hours to the dates. You may subtract the dates
SELECT sysdate+2 "add_days" FROM DUAL;
SELECT sysdate-3 "sub_days" FROM DUAL;
SELECT sysdate+3/24 "add_hours" FROM DUAL;
SELECT sysdate-2/24 "sub_hours" FROM DUAL;
SELECT sysdate-hire_date "sub_dates" FROM EMPLOYEES; -- returns variety of days between the 2 dates.
3. MONTHS_BETWEEN
The Months_Between perform returns the variety of months between the 2 given dates.
Syntax: months_between(date1,date2)
SELECT months_between(sysdate,hire_date) FROM EMPLOYEES:
SELECT months_between('01-JUL-2000', '23-JAN-2000') FROM DUAL;
4. ADD_MONTHS
The Add_Months is used so as to add or subtract the variety of calendar months to the given date.
Syntax: add_months(date,n)
SELECT add_months(sysdate,3) FROM DUAL;
SELECT add_months(sysdate,-3) FROM DUAL;
SELECT add_months('01-JUL-2000', 3) FROM DUAL;
5. NEXT_DAY
The Next_Day perform finds the date of the following specified day of the week. The syntax is
NEXT_DAY(date,’char’)
The char generally is a character string or a quantity representing the day.
SELECT next_day(sysdate,'FRIDAY') FROM DUAL;
SELECT next_day(sysdate,5) FROM DUAL;
SELECT next_day('01-JUL-2000', 'FRIDAY') FROM DUAL;
6. LAST_DAY
The Last_Day perform returns the final day of the month.
SELECT last_day(sysdate) FROM DUAL;
SELECT last_day('01-JUL-2000') FROM DUAL;
7. ROUND
The Round perform returns the date rounded to the desired format. The Syntax is
Round(date [,’fmt’])
SELECT spherical(sysdate,'MONTH') FROM DUAL;
SELECT spherical(sysdate,'YEAR') FROM DUAL;
SELECT spherical('30-OCT-85','YEAR') FROM DUAL;
8. TRUNC
The Trunc perform returns the date truncated to the desired format. The Syntax is
Trunc(date [,’fmt’])
SELECT trunc(sysdate,'MONTH') FROM DUAL;
SELECT trunc(sysdate,'YEAR') FROM DUAL;
SELECT trunc('01-MAR-85','YEAR') FROM DUAL;
The Oracle Conversion and General Functions are coated in different sections. Go via the hyperlinks Oracle Conversion Functions and Oracle General Functions.