Single Row Functions in ORACLE SQL

The single row functions are categorized into

Character Functions: Character functions accept character inputs and can return either character or number values as output.

Number Functions: Number functions accepts numeric inputs and returns only numeric values as output.

Date Functions: Date functions operate on date data type and returns a date value or numeric value.

Conversions Functions: Converts from one data type to another data type.

General Functions

Let see each function with an example:

Character Functions Example

1. LOWER

The Lower function converts the character values into lowercase letters.

SELECT lower('ORACLE') FROM DUAL;

2. UPPER

The Upper function converts the character values into uppercase letters.

SELECT upper('oracle') FROM DUAL;

3. INITCAP

The Initcap function coverts the first character of each word into uppercase and the remaining characters into lowercase.

SELECT initcap('LEARN ORACLE') FROM DUAL;

4. CONCAT

The Concat function coverts the first string with the second string.

SELECT concat('Oracle',' Backup) FROM DUAL;

5. SUBSTR

The Substr function returns specified characters from character value starting at position m and n characters long. If you omit n, all characters starting from position m to the end 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 value as negative. In this case the count starts from the end of the string.

SELECT substr('ORACLE BACKUP',-6) FROM DUAL;

6. LENGTH

The Length function is used to find the number of characters in a string.

SELECT length('Oracle Data Guard') FROM DUAL;

7. INSTR

The Instr function is used to find the position of a string in another string. Optionally you can provide position m to start searching for the string and the occurrence n of the string. By default m and n are 1 which means to start the search at the beginning of the search and the first occurrence.

Syntax: instr('Main String', 'substring', [m], [n])

SELECT instr('oralce apps','app') FROM DUAL;

SELECT instr('oralce apps is a great application','app',1,2) FROM DUAL;

8. LPAD

The Lpad function pads the character value right-justified to a total width of n character positions.

Syntax: lpad(column, n, 'string');

SELECT lpad('100',5,'x') FROM DUAL;

9. RPAD

The Rpad function pads the character value left-justified to a total width of n character positions.

Syntax: rpad(column, n, 'string');

SELECT rpad('100',5,'x') FROM DUAL;

10. TRIM

The Trim function removes the leading or trailing or both the characters from a string.

Syntax: trim(leading|trailing|both, trim_char from trim_source)

SELECT trim('O' FROM 'ORACLE') FROM DUAL;

11. REPLACE

The Replace function is used to replace a character with another character in a string.

Syntax: replace(column, old_char,new_char)

SELECT replace('ORACLE DATA BACKUP', 'DATA','DATABASE') FROM DUAL;

Number Functions Example

1. ROUND

The Round function rounds the value to the n decimal values. If n is not specified, there won't be any decimal places. If n is negative, numbers to the left of the decimal point are rounded.

Syntax: round(number,n)

SELECT round(123.67,1) FROM DUAL;

SELECT round(123.67) FROM DUAL;

SELECT round(123.67,-1) FROM DUAL;

2. TRUNC

The Trunc function truncates the value to the n decimal places. If n is omitted, then n defaults to zero.

Syntax: trunc(number,n)

SELECT trunc(123.67,1) FROM DUAL;

SELECT trunc(123.67) FROM DUAL;

3. MOD

The Mod function returns the remainder of m divided by n.

Syntax: mod(m,n)

SELECT mod(10,5) FROM DUAL;

Date Functions Example

1. SYSDATE

The Sysdate function returns the current oracle database server date and time.

SELECT sysdate FROM DUAL;

2. Arithmetic with Dates

You can add or subtract the number of days or hours to the dates. You can also 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 number of days between the two dates.

3. MONTHS_BETWEEN

The Months_Between function returns the number of months between the two 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 to add or subtract the number 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 function finds the date of the next specified day of the week. The syntax is

NEXT_DAY(date,'char')

The char can be a character string or a number 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 function returns the last day of the month.

SELECT last_day(sysdate) FROM DUAL;

SELECT last_day('01-JUL-2000') FROM DUAL;

7. ROUND

The Round function returns the date rounded to the specified format. The Syntax is

Round(date [,'fmt'])

SELECT round(sysdate,'MONTH') FROM DUAL;

SELECT round(sysdate,'YEAR') FROM DUAL;

SELECT round('30-OCT-85','YEAR') FROM DUAL;

8. TRUNC

The Trunc function returns the date truncated to the specified 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;