Oracle SQL Functions Overview

Posted on 30. Dec, 2010 by in Database

Print This Post Print This Post

VN:F [1.6.3_896]
Rating: 9.7/10 (3 votes cast)

Oracle Database provides various built-in functions to perform special operations such as conversions, calculations, transformations, and comparisons.

Single Row Functions

Single row functions refer to functions that act on a single row of data, as opposed to multi-row functions which act of a set of data rows. This section covers many of the most common single-row functions.

UPPER

Purpose

The UPPER function is used to convert all lowercase letter in a specified string to upper case. Letters that are already uppercase, numbers and symbols will not be changed. This function can be useful when comparing strings of uncertain case. For example, if you are searching for a string “John” but you don’t know if it is stored as John, john or JOHN, you can use the UPPER function to convert the string to all uppercase.

Usage

 

UPPER(String)

Examples

 

SELECT UPPER(FIRST_NAME) FROM EMPLOYEES;

 

SELECT * FROM EMPLOYEES WHERE UPPER(FIRST_NAME)=’JOHN’;

 

LOWER

Purpose

The LOWER function is used to convert all uppercase letter in a specified string to lower case. Letters that are already lowercase, numbers and symbols will not be changed. Like the UPPER function, this function may be useful when comparing strings of uncertain case. For example, if you are searching for a string “John” but you don’t know if it is stored as John, john or JOHN, you can use the LOWER function to convert the string to all lowercase.

 

Usage

LOWER(String)

Examples

 

SELECT LOWER(FIRST_NAME) FROM EMPLOYEES;

 

SELECT * FROM EMPLOYEES WHERE LOWER(FIRST_NAME)=’john’;

 

INITCAP

Purpose

The INITCAP function is used to convert strings so that the first letter of each word is capitalized and the proceeding letters in each word are lowercase. This function is useful when formatting proper names that may be stored in the database as either all uppercase or all lowercase. For example,”JAMES” would be converted to James, or joshua would be converted to Joshua.

Usage

INITCAP(String)

Example

 

SELECT INITCAP(FIRST_NAME), INITCAP(LAST_NAME) FROM EMPLOYEES;

 

LENGTH

Purpose

The LENGTH function returns an integer value representing the number of characters (or length) of a specified string. For example, the length of the string “Michael” would be 7.

Usage

LENGTH(String)

Example

SELECT LENGTH(FIRST_NAME) FROM EMPLOYEES;

 

SUBSTR

Purpose

The SUBSTR function returns a part (or sub-string) of another string. You must specify the source string, the start position, and the length of the new string. For example, “United States” is a sub-string of “The United States of America” (see below).

Usage

SUBSTR(String, Start_Position, Length)

 

Example

SELECT SUBSTR(‘The United States of America’, 5, 13) FROM DUAL;

 

LPAD

Purpose

The LPAD function can be used to return a substring of another string starting at the beginning of the specified string with a specified length, and/or can add specified characters (or pad) to the left side.

Usage

LPAD(String, Length) of LPAD(String, Length, Pad_String)

Example

SELECT LPAD(‘Joshua’, 4) FROM DUAL;

 

Returns: Josh

 

SELECT LPAD(‘Carmon’, 10, ‘Mr. ‘) FROM DUAL;

 

Returns: Mr. Carmon

 

SELECT LPAD(‘L’, 3, ‘X’) FROM DUAL;

 

Returns: XXL

 

RPAD

Purpose

The RPAD function can be used to add specified characters (or pad) to the right side of a string.

Usage

RPAD(String, Length) of RPAD(String, Length, Pad_String)

Example

SELECT RPAD(‘Joshua’, 4) FROM DUAL;

 

Returns: Josh

 

SELECT RPAD(‘Tony’, 7, ‘ Jr.‘) FROM DUAL;

 

Returns: Tony Jr.

 

SELECT RPAD(‘L’, 3, ‘X’) FROM DUAL;

 

Returns: LXX

 

INSTR

Purpose

The INSTR function searches one string to see if it contains another string. If the substring is found, it returns the position within the larger string. Otherwise, it returns 0. You may also specify a position in the string to start searching for the substring, and specify an occurrence (for example find the 3rd occurrence of the word “the”).

Usage

INSTR(String, Sub_String)

 

or

 

INSTR(String, Sub_String, Start)

 

or

 

INSTR(String, Sub_String, Start, Occurance)

Example

SELECT INSTR(‘A Haystack’, ‘Needle’) FROM DUAL;

 

Returns: 0

 

SELECT INSTR(‘Row Row Row Your Boat’, ‘Boat’, 12) FROM DUAL;

 

Returns: 18

 

SELECT INSTR(‘Row Row Row Your Boat’, ‘Row’, 1, 3) FROM DUAL;

 

Returns: 9

 

TRIM

Purposes

The TRIM function will remove specified characters from either the beginning or the end of a string or both. If no character is specified it will remove spaces.

Usage

TRIM(String)

 

Or

 

TRIM(Leading Remove_String FROM String)

 

Or

 

TRIM(Trailing Remove_String FROM String)

 

Or

 

TRIM(Both Remove_String FROM String)

 

Example

 

SELECT TRIM(‘ Remove Spaces ‘) FROM DUAL;

 

Returns: Remove Spaces

 

SELECT TRIM(Leading ‘0’ FROM ‘000123’) FROM DUAL;

 

Returns: 123

 

SELECT TRIM(Trailing ‘0’ FROM ‘123000’) FROM DUAL;

 

Returns: 123

 

REPLACE

Purpose

The REPLACE function performs a find/replace operation on a string. A specified string is searched for the occurrence of a specified substring, and if found that substring is replaced with a specified replacement-string.

Usage

REPLACE(String, String_to_Replace, Replacement_String)

 

Note: If Replacement_String is omitted then the String_to_Replace will simply be removed.

Example

SELECT REPLACE(‘I will not study, and will not pass’, ‘not’, ‘always’) FROM DUAL;

 

Returns: I will always study, and will always pass

 

CONCAT

Purpose

The CONCAT function concatenates (or combines) two strings together.

Usage

CONCAT(String1,String2)

Example

SELECT CONCAT (‘abc’, ‘123’) FROM DUAL;

 

Returns: abc123

TO_CHAR

Purpose

The TO_CHAR function will accept a date or number value as an input and converts it into a string.

Usage

TO_CHAR(Value) OR TO_CHAR(Value, Format)

Examples

SELECT TO_CHAR(27) FROM DUAL;

Returns: “27” as a string

 

SELECT TO_CHAR(27.159, ’99.9’) FROM DUAL;

Returns: “27.1” as a string

 

SELECT TO_CHAR(27.159, ’$99.99’) FROM DUAL;

Returns: “$27.15” as a string

 

SELECT TO_CHAR(SYSDATE, ’MM/DD/YYYY’) FROM DUAL;

Returns: “12/31/2010” as a string

 

SELECT TO_CHAR(SYSDATE, ‘DAY’) FROM DUAL;

Returns: “TUESDAY” as a string

 

Below is a table of common date/time formatting options:

 

DD Day of month (1-31)
MM Numeric Month (1-12)
MON Month Abbreviation (3 char)
MONTH Name of Month
YY 2-digit Year
YYYY 4-digit Year
HH Hours (1-12)
MI Minutes (0-59)
SS Seconds (0-59)
DAY Name of Day
DY Abbreviated Name of Day

 

 

 

TO_NUMBER

Purpose

The TO_NUMBER function converts a number in string format into a number.

Usage

TO_NUMBER(String_Number) OR TO_NUMBER(String_Number, Format)

Example

SELECT TO_NUMBER(’15’) FROM DUAL;

Returns: 15 as a number

 

SELECT TO_NUMBER(’15.123’, ’99.9’) FROM DUAL;

Returns: 15.1 as a number

 

 

TO_DATE

Purpose

The TO_DATE function converts a date in a string format into a date format.

Usage

TO_DATE(String_Date)

Example

SELECT TO_DATE(’15-DEC-2009’) FROM DUAL;

 

ROUND

Purpose

The ROUND function will round a number to a specified number of decimal places. It can also be used to round dates to a specified date precision (day, month, year, etc.).

Usage

ROUND(Number, Decimal_Places)

 

Or

 

ROUND(Date, Date_Precision)

Example

SELECT ROUND(2.5) FROM DUAL;

 

Returns: 3

 

SELECT ROUND(3.14159265, 2) FROM DUAL;

 

Returns: 3.14

SELECT ROUND(TO_DATE(’25-DEC-09′), ‘MONTH’) FROM DUAL;

 

Returns: 01-JAN-10

 

TRUNC
Purpose

The TRUNC function will truncate (or round down) a number to a specified number of decimal places. It can also be used to round truncate (or round down) dates to a specified date precision (day, month, year, etc.).

Usage

TRUNC(Number, Decimal_Places)

 

Or

 

TRUNC(Date, Date_Precision)

Example

SELECT TRUNC(2.5) FROM DUAL;

 

Returns: 2

 

SELECT TRUNC(3.14159265, 2) FROM DUAL;

 

Returns: 3.14

SELECT TRUNC(TO_DATE(’25-DEC-09′), ‘MONTH’) FROM DUAL;

 

Returns: 01-DEC-09

 

MOD

Purpose

The MOD function is used to return the remainder of the division of one number by another.

Usage

MOD(a,b)

Example

SELECT MOD(19,8) FROM DUAL;

 

Returns: 3

 

MONTHS_BETWEEN

Purpose

The MONTHS_BETWEEN function returns the number of months between two dates.

Usage

MONTHS_BETWEEN(date1, date2)

Example

SELECT MONTHS_BETWEEN(TO_DATE(’12-MAR-2009′), TO_DATE(’12-MAR-2006′)) FROM DUAL;

 

Returns: 36

 

ADD_MONTHS

Purpose

The ADD_MONTHS function takes a specified date and adds a specified number of months to that date.

Usage

ADD_MONTHS(Date, Number)

Example

SELECT ADD_MONTHS(TO_DATE(’14-OCT-2009′), 4) FROM DUAL;

 

Returns: 14-FEB-2010

 

LAST_DAY

Purpose

The LAST_DAY function returns the last day of the month of a given date.

Usage

LAST_DAY(Date)

Example

SELECT LAST_DAY(TO_DATE(’05-JAN-2010’)) FROM DUAL;

 

Returns: 31-JAN-2010

 

NEXT_DAY

Purpose

The NEXT_DAY function returns the next occurrence of a specified weekday given a date.

Usage

NEXT_DAY(Date, Weekday)

Example

SELECT NEXT_DAY(TO_DATE(’01-JAN-2010’), ‘WEDNESDAY’) FROM DUAL;

 

Returns: 06-JAN-2010

 

SYSDATE

Purpose

The SYSDATE function returns the current system date/time from the database system.

Usage

SYSDATE

Example

SELECT SYSDATE FROM DUAL;

 

NVL

Purpose

The NVL function checks a string to see if it is NULL, and if so it will substitute a specified value.

Usage

NVL(String, Substitute)

Example

SELECT NVL(MIDDLE_NAME, ‘N/A’) FROM EMPLOYEES;

 

NVL2

Purpose

The NVL2 function checks a string to see if it is NULL, and will return a specified value if NULL, and another specified value if NOT NULL.

Usage

NVL2(String, NOT_NULL_VALUE, NULL_VALUE)

Example

SELECT NVL2(MIDDLE_NAME, ‘Has Middle Name’, ‘Does not have middle name’;

 

COALESCE

Purpose

The COALESCE function evaluates a list of values, and returns the first item in the list that is NOT NULL.

Usage

COALESCE(Value1, Value2, Value3…)

Example

SELECT COALESCE(HOME_PHONE, WORK_PHONE, MOBILE_PHONE) FROM CUSTOMERS;

 

Results: If home phone is not null it will return home phone. If home phone is null, but work phone is not null it will return work phone. If home and work are null, but mobile phone is not null it will return mobile phone. If they are all null it will return null.

 

DECODE

 

Purpose

The DECODE function compares an expression against a list of values, and returns a specified value when a match is found, and can optionally return a default value if no match is found.

Usage

DECODE(Expression, value1, result1, value2, result2, value3, result3…[default])

Example

SELECT FIRST_NAME, LAST_NAME, DECODE(DEPARTMENT_ID, 1, ‘Sales’, 2, ‘Accounting’, 3, ‘IT’, ‘Other’) FROM EMPLOYEES;

 

 

Aggregate Functions

Aggregate Functions are multi-row functions that perform aggregate operations on groups of data.

 

COUNT

Purpose

The COUNT function counts and returns the number of rows resulting from a query.

Usage

COUNT(*) OR COUNT(Field)

Example

SELECT COUNT(*) FROM EMPLOYEES WHERE DEPARTMENT_ID=3;

 

Returns: the number of employees in department 3.

SUM

Purpose

The SUM function calculates the sum of the values returned by a query.

Usage

SUM(Field or Expression)

Example

SELECT SUM(SALARY) FROM EMPLOYEES;

 

Returns: The total company payroll.

AVG

Purpose

The AVG function calculates the average of the values returned by a query.

Usage

AVG(Field or Expression)

Example

SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID=1;

 

Returns: The average salary of employees in department 1.

MAX

Purpose

The MAX function returns the maximum or highest value from the results of a query.

Usage

MAX(Field or Expression)

Example

SELECT MAX(SALARY) FROM EMPLOYEES;

 

Returns: the highest salary in the employee table.

MIN

Purpose

The MIN function returns the minimum or lowest value from the results of a query.

Usage

MIN(Field or Expression)

Example

SELECT MIN(SALARY) FROM EMPLOYEES;

 

Returns: the lowest salary in the employee table.

STDDEV

Purpose

The STDDEV function returns the standard deviation from a set of values resulting from a query.

Usage

STDDEV(Field or Expression)

Example

SELECT STDDEV(SALARY) FROM EMPLOYEES;

Returns the standard deviation of employee salaries.

VN:F [1.6.3_896]
Rating: 9.7/10 (3 votes cast)

Tags: , ,

Print This Post Print This Post

One Comment

Inez C. Ellison

11. Oct, 2013

The namespace-uri function returns the namespace URI of the expanded-name of the node in the argument node-set that is first in document order . If the argument node-set is empty, the first node has no expanded-name , or the namespace URI of the expanded-name is null, an empty string is returned. If the argument is omitted, it defaults to a node-set with the context node as its only member.

Leave a reply