How to Use Functions in Oracle

  • google plus

In Brief...

The Oracle database supports dozens of functions. The majority of the functions are "scalar", or column, functions as opposed to "aggregate", or group functions such as count. In this topic I'll introduce you to a couple of useful scalar functions.

Take our Advanced Oracle SQL Queries course for free.

See the Course Outline and Register

Instructions

To learn how to use the functions in Oracle, follow these steps:

  1. You'll need to download and install Oracle 12c. The instructions for the setup can be found in How to use sample schemas. Follow steps 1 through 4 in this topic before proceeding to the next step.
  2. Let's presuppose we are required to combine the first name and last name of employees stored in the Employees table. First, we will issue an SQL*Plus statement to limit the column size on output for readability:
    Format out with col statement
  3. We will combine first name and last name (with a white space separator) using the concatenation operator (||) for department ID = 60:
    Display first name concatenated with last name
  4. Imagine we are now directed to display the first character only of the first name:
    Display first character of first name concatenated with last name
    Notice we have used the substr (substring) function to display the substring beginning in position 1 of the first name for a length of 1.

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, XML, Windows, Java, Adobe, HTML5, JavaScript, Angular, and much more. Check out our complete course catalog.

Categories

Courses

Author: Stephen Withrow

Stephen has over 30 years' experience in training, development, and consulting in a variety of technology areas including Java, C, C++, XML, JavaScript, AJAX, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen is a published writer in both technical and non-technical endeavors. Stephen received an undergraduate degree in Computer Science and Physics from Florida State University.

Discuss