Oracle Single Row Functions Examples

  • Updated
  • Posted in Programming
  • 6 mins read


Oracle offers single row features to govern the information values. The single row features function on single rows and return just one end result per row. In normal, the features take a number of inputs as arguments and return a single worth as output. The arguments generally is a user-supplied fixed, variable, column identify and an expression.

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:

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.

Leave a Reply