Single-Row Functions | SQL Functions | Number Functions | Character Functions | Date Functions | Conversion Functions

 

 SQL Functions:

What is a function?

Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format enables them to operate on zero, one, two, or more arguments:

Syntax: function (argument, argument, ...)

SQL Functions:

Functions are classified into two types:

•       System defined Functions

•       User Defined Functions.

System Defined Function:

•       A Function which is defined by System is known as System  defined Function.

User Defined Function:

•       A Function which is defined by User is known as User  defined Function

There are Two types of functions in oracle:

1.Single-Row Functions.

2.Multi-Row Functions.

 Single-Row Functions

• ·       Manipulate data items.
• ·       Accept arguments and return one value.
• ·       Act on each row returned.
• ·       Return one result per row.
• ·       May modify the data type.
• ·       Can be nested.
• ·       Accept arguments which can be a column or an expression.

Number Functions :

Numeric functions accept numeric input and return numeric values. Most numeric functions that return NUMBER values that are accurate to 38 decimal digits

ABS Function:

• Definition: Returns the absolute value of a number.
• Syntax: ABS(number)
• Example: SELECT ABS(-10) FROM dual;
• --(Result: 10)
• 
  

ROUND Function:

• Definition: Rounds a number to a specified number of decimal places.
• Syntax: ROUND(number, [decimal_places])
• Example: SELECT ROUND(15.789, 1) FROM dual;
• --(Result: 15.8)

CEIL (or CEILING) Function:

• Definition: Rounds a number up to the nearest integer.
• Syntax: CEIL(number)
• Example: SELECT CEIL(12.345) FROM dual;
• --(Result: 13)

FLOOR Function:

• Definition: Rounds a number down to the nearest integer.
• Syntax: FLOOR(number)
• Example: SELECT FLOOR(18.76) FROM dual;
• --(Result: 18)
• 
  

MOD Function:

• Definition: Returns the remainder of the division of two numbers.
• Syntax: MOD(dividend, divisor)
• Example: SELECT MOD(17, 5) FROM dual;
• --(Result: 2)

TRUNC Function:

• Definition: Truncates a number to a specified number of decimal places.
• Syntax: TRUNC(number, [decimal_places])
• Example: SELECT TRUNC(123.456, 2) FROM dual;
• --(Result: 123.45)

Character Function :

Character functions that return character values return values of the same data type as the input argument. The length of the value returned by the function is limited by the maximum length of the data type returned.

Some of them are

CONCAT – CONCAT(‘A’,’B’)                                  -- AB

LOWER   – LOWER(‘ABC’)                                   -- abc

UPPER    –  UPPER(‘abc’)                                    -- ABC

SUBSTR  -  SUBSTR('ABCDEFG',3 , 4)                -- CDEF

LPAD function :

The Oracle LPAD() function is used to padding  the leftt side of a string with a specific set of 

characters. The function is useful for formatting the output of a query.

Syntax:

SELECT LPAD('Oracle',10,'+') FROM DUAL;

Sample Output:

++++Oracle

RPAD function :

The Oracle RPAD function is used to padding the right side of a string with a specific set of characters. This function is useful for formatting the output of a query.

The string cannot be null. If expr2 not specified then it defaults to a single blank. When expr1 is longer than n, then this function returns the portion of expr1 that fits in n.

SELECT last_name, RPAD(' ', salary/1000/1, '*') "Salary"

FROM employees

WHERE department_id = 80

ORDER BY last_name, "Salary";

UPPER function :

The Oracle UPPER () function returns a specified character expression in UPPERCASE letters.

Examples: Oracle UPPER function

SELECT UPPER (last_name) "Uppercase"

FROM employees;

Sample Output:

Uppercase

----------------

ABEL

ANDE

ATKINSON

AUSTIN

BAER

BAIDA

BANDA

BATES

. . .

     

LOWER function :

The Oracle LOWER () function returns a specified character expression in lowercase letters.

Examples: Oracle LOWER function

SELECT LOWER(first_name) FROM employees;

Sample Output :

LOWER(FIRST_NAME)

--------------------

ellen

sundar

mozhe

david

hermann

shelli

amit

elizabeth

sarah

david

......

INITCAP function :

The Oracle NLS_INITCAP () function sets the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

Examples: Oracle INITCAP function

SELECT NLS_INITCAP('ijzer') "Initcap" FROM DUAL;

Sample output:

Ijzer

LENGTH function :

The Oracle LENGTH function is used to return the length of a given string. If the string has data type CHAR, then the length includes all trailing blanks. If a string is null, then this function returns null.

Example: Oracle LENGTH function

SELECT LENGTH(globalsparktek) "Length in characters"

FROM DUAL;

Sample Output:

Length in characters

--------------------

          14

SUBSTR function :

The SUBSTR functions returns the specified number (substring_length) of characters from a particular position of a given string. 

Examples: Oracle SUBSTR function

SELECT SUBSTR(‘globalspar',3,4) "Substring"

FROM DUAL;

Sample Output:

Substring

---------

obal

INSTR function :

The Oracle INSTR function is used to search string for substring and find the location of the substring in the string.

Example: Oracle INSTR function

SELECT INSTR('THIS IS THE THING','TH') "Position Found"

FROM DUAL;

Sample Output:

Position Found

--------------

        1

RTRIM function :

The Oracle RTRIM function is used to remove spaces( if no character(s) is provided as trimming_text ) or set of characters which are matching with the trimming_text, from the end of a string.

Example: Oracle RTRIM function

1.SELECT RTRIM('GST  ') "Str" FROM DUAL;

Sample Output:

Str

---

GST

2.SELECT RTRIM('GST123486','0123456789') "Str" FROM DUAL;

Sample Output:

Str

---

GST

LTRIM function :

The Oracle LTRIM() function is used to remove all specified characters from the left end side of a string. Optionally you can specify an initial character or characters to trim to, or it will default to a blank.

Example: Oracle LTRIM function

1.SELECT LTRIM('  GST') "Str" FROM DUAL;

Str

---

GST

2.SELECT LTRIM('0101985','01') "Str" FROM DUAL;

Str

---

985

3.SELECT LTRIM('125486GST','0123456789') "Str" FROM DUAL;

Str

---

GST

4.SELECT LTRIM('  GST   ') "Str" FROM DUAL;

Str

------

GST 

TRIM function :

The Oracle TRIM function is used to remove all leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then it is necessary to enclose it in single quotation marks

Example: Oracle TRIM function

SELECT TRIM('   removing leading and trailing white spaces    ') FROM DUAL;

Sample Output:

------------------------------------------

removing leading and trailing white spaces

REPLACE function :

 

The REPLACE function is used to return char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. When search_string is null, then char is returned.

Example: Oracle REPLACE function

SELECT REPLACE('MAN and MAT','M','F') "New String"

 FROM DUAL;

Sample Output:

New String

-----------

FAN and FAT

Date Functions :

• Date-time functions operate on date (DATE), timestamp (TIMESTAMP,
•  TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.

Conversion Functions :

Implicit Data Type Conversion :

For assignments, the Oracle server can automatically convert the following data type of values:

•       CHAR to NUMBER conversion

•       CHAR to DATE conversion

       •     Varchar2 or char  to Number

       •     Varchar2 or char  to Date

       •    Number to Varchar2

       •    Date  to Number

Explicit Data Type Conversion :

 

Using the TO_CHAR Function with Dates 

Syntax : TO_CHAR(date, 'format_model')

The format model:

Must be enclosed in single quotation marks and is case sensitive

Can include any valid date format element

Is separated from the date value by a comma.

Elements of the Date Format Model :