SQL Functions are Oracle Database’s built-in functions which can be used with SQL statements. These functions are divided into various types and categories. Let’s see what are those –

Types of SQL Functions

In Oracle Database SQL functions are divided into two categories. These two catagories are –

  1. Single Row Functions
  2. Multiple row functions (Aggregate functions)

What are Single Row Functions?

Single-row functions operate on one row at a time and return one row of output for each input row. In addition they are capable of manipulating data items and modifying the data types. Furthermore these functions return one result per row and act on each row that is returned. Single row functions can accept one or more arguments and can return one value. These arguments can be a column or an expression.

Types of Single Row Functions

With oracle we have 5 types of Single Row Functions

  1. Character Functions
  2. Number Functions
  3. General Functions
  4. Conversion Functions and
  5. Date Functions.

sql functions in oracle database by manish sharma

Types of Character Functions

Character Functions are further divided into two categories.

  1. Case manipulation functions
  2. Character Manipulation functions.

In Oracle Database we have three Case Manipulation functions and seven Character Manipulation functions. The three Case Manipulation functions are –

  1. Lower()
  2. upper () and
  3. initcap ()

And seven Character Manipulation functions are –

  1. concat(),
  2. substr(),
  3. length(),
  4. instr(),
  5. lpad() | rpad(),
  6. trim ()and
  7. replace().

That’s it for this tutorial. Hope you learnt something new. Make sure to read the next blog to learn how to use CONCAT() function as well as the differences between Concat () function and Concat operator.

Also make sure to subscribe to my YouTube channel for many interesting computer programming tutorials. Thanks & have a great day!