Single Row Functions

Single row functions.. Ugh ? . Before we even look at single row functions, let us begin with understanding what a function is (If you have some programming background, you can skip this). A function is a small program written and called upon whenever needed. Still confusing, right ? . Don’t worry. Let us break it down. Functions are used to achieve only a specific purpose and whenever we need to achieve this purpose, we use them. For example, if we need to add, we call a function for adding; if we need to know the average of numbers, we call on the function for calculating the average; if we need our letters to be in uppercase, we use the function to help us with that, etc. Functions, are small programs that help us do various activities whenever we need to. Has it now crossed your mind that when you’re using MS. Word and you change the colour of text or change the letter casing you’re actually using functions without knowing ?. In MS. Excel, we often use these functions like SUM(), COUNT(), MIN(), etc. A function will always take on parameters or arguments to perform its work. Parameters are what is wrapped inside the brackets of a function. For example, the SUM() function. Whatever is written inside the brackets is what is referred to as a parameter. Parameters could be one or more for a function.

After knowing what a function is, let us get back to Oracle with single row functions. In Oracle, we have a number of functions that help us do different tasks like adding numbers, changing letter casing, working with dates, and so much more as we shall be seeing. These functions are categorised into single row functions and group row functions (There are other categories of functions we shall not cover like the analytical functions, national language functions, etc). In this episode, let us focus on the single row functions.

Single row functions are functions that work on a single row of data retrieved. For example, we have this kind of data set below:

school_id	school_name	school_location
1	Africa Junior School	Namagoma
2	Salfah Islamic School	Mpigi
3	Nabisunsa Girls School

Single row functions will only work on a row of data. For example, we want the names of the schools to be in uppercase. We shall use the UPPER() function that will convert the school names row by row. If we need to give the location to a school that doesn’t have one, it will be executed row by row. In other-words, single row functions work on a single record after another in the database.

After understanding how single row functions operate, we have a number of single row functions that we can use. These are character functions that work with characters, number functions that work with numbers, date functions that handle dates and conversion functions to convert from one data type to another. Single row functions can be used in the SELECT, WHERE and ORDER BY clauses of the SELECT statement.

Character Functions

LOWER

This function converts characters to lower case. It takes one argument or parameter i.e. LOWER(x) where x is the character string to be converted in lowercase. For example, when we check out data in the email column of the employees table, email addresses are in uppercase. This shouldn’t be like that. We need to have them in lower case. And here is where our LOWER function comes handy. We take the email column in the LOWER function to be the parameter. Here is the code:

SELECT first_name, email, LOWER(email) FROM employees;

From the above code, the email column shows how emails look like now. With the LOWER(email), emails have been converted to lower case. But watch the heading for lowercase emails in the output. Can’t we make our output look better and more meaningful ? . Remember column alias? There you go ?

SELECT first_name, email, LOWER(email) AS "NEW EMAIL" FROM employees;

UPPER

This function converts characters to upper case. It takes one argument or parameter i.e. UPPER(x) where x is the character string to be converted in uppercase. For example, we need to have all last names in upper case. We take the last_name column as the parameter to be worked on.

SELECT UPPER(last_name) AS LAST_NAME, first_name FROM employees;

INITCAP

This function converts the first character of a character string to upper case and the rest in lower case i.e. sentence case. It takes one argument or parameter i.e. INITCAP(x) where x is the character string to be converted in sentence case. For example, we need to convert all job_ids in the jobs table to sentence case.

SELECT job_id "CURRENT JOB_ID", INITCAP(job_id) "NEW JOB_ID" FROM employees;

SUBSTR

It takes three arguments i.e. SUBSTR(x, y {, z}). This function returns a portion of a character string x that is z characters long starting at position y. For example, lets return a portion of department names with 7 characters from position 4.

SELECT department_name, SUBSTR(department_name, 4, 7) AS PORTION FROM departments;

Parameter z in SUBSTR(x, y {, z}) is optional. If not specified, the function considers the remaining characters in x from position y to be the z value. Let us modify our previous example to include this bit (omitting parameter z. Remember it determines how long a returned string will be).

SELECT department_name, SUBSTR(department_name, 4, 7) "OLD PORTION", SUBSTR(department_name, 4) "NEW PORTION" FROM departments;

LPAD

The function takes three arguments ie. LPAD(x, y {,z}). It returns character string x that is expanded into y characters long using z to fill in the remaining space on the left side of x in case x doesn’t make y characters. If x is more than the specified characters in y, it is truncated to y characters. Confused, right ? ; don’t worry. Let use an example. Lets say we want all first_names to be 10 characters long. For those that don’t have ten characters, # symbol will be used to fill in the remaining space to make 10 characters.

SELECT last_name, LPAD(last_name, 10, '#') PADDING FROM employees;

Parameter z in LPAD(x, y {, z}) is optional. If not specified, the function uses default space as the fill-in character. Let us modify our previous example to include this bit (omitting parameter z).

SELECT last_name, LPAD(last_name, 10, '#') PADDING, LPAD(last_name, 10) NEW_PADDING FROM employees;

RPAD

Works the same way as the LPAD. However, this time the filling happens at the right side of the x string. The example below adds a * to the right side of the salary column for any salary that doesn’t make it to 6 figures.

SELECT last_name, RPAD(salary, 6, '*') SALARY FROM employees;

LTRIM

It takes two parameters i.e. LTRIM (x {, y}). It returns x without leading characters specified in y. In case no leading characters specified in y appear in x, x is returned unchanged. The y parameter is optional and if omitted, x will be returned unchanged. For example, lets use remove ‘Gr’ from all last names that start with letter G.

SELECT last_name, LTRIM(last_name, 'Gr') FROM employees WHERE last_name LIKE 'G%'; 

RTRIM

It works the same way as LTRIM. However, this time the trimming happens on the right side of the x value (removes the trailing characters specified in y that appear in x. Lets remove ‘es’ from all last names ending with ‘es’.

SELECT last_name, RTRIM(last_name, 'es') FROM employees WHERE last_name LIKE '%es'; 

LENGTH

It takes one argument i.e. LENGTH (x). It returns the number of characters in x. Let us get the number of characters for each department name. Even a space is considered a character.

SELECT department_name, LENGTH(department_name) CHARACTERS FROM departments;

CONCAT

It takes two arguments i.e. CONCAT(x, y). It joins x to y. If x is NULL, y is returned and vice-versa. If both are NULL, NULL will be returned.

SELECT CONCAT(first_name, last_name) AS "Employee Names" FROM employees;

From the output, both names are joined without a space. We can include this space by nesting the CONCAT function. Remember, CONCAT can only take two parameters. The inner CONCAT function will take the first name appended with a space. Then the second one will consider CONCAT(first_name, ‘ ‘) as parameter x and the last name as parameter y.

SELECT CONCAT(CONCAT(first_name, ' '), last_name) AS "Employee Names" FROM employees;

We can achieve the same using concatenation operator i.e ||.

SELECT first_name ||' '|| last_name AS "Employee Names" FROM employees;

REPLACE

It takes three arguments i.e. REPLACE (x, y, z). It returns x with all occurrences of y replaced with z. Let us replace all occurrences of es with 4% in the department names that have ‘es’ in their names.

SELECT department_name OLD, REPLACE(department_name, 'es', '4%') NEW FROM departments WHERE department_name LIKE '%es%';

Number Functions

ABS

Takes a single value. Returns absolute (positive) values.

SELECT -2000 negative, ABS(-2000) positive FROM dual;

A dual table is a dummy table available to all users in Oracle. It is used for testing purposes and experiments.

FLOOR

Takes one parameter i.e FLOOR(x). It returns a whole number that is lower or equal to x. For example, we need to get the floor of 32.8. To do that, we have numbers: 32, 32.1, 32.2, … 32.8, 32.9, 33. So, the whole number that is lower than 32.8 is 32.

SELECT FLOOR(32.8) FLOOR FROM dual;

CEIL

Takes one parameter i.e CEIL(x). It returns a whole number that is greater or equal to x. For example, we need to get the ceil of 32.8. To do that, we have numbers: 32, 32.1, 32.2, … 32.8, 32.9, 33. So, the whole number that is greater than 32.8 is 33.

SELECT CEIL(32.8) CEIL FROM dual;

ROUND

It takes two parameters i.e. ROUND(x, y). It returns x rounded to y digits to the right of the decimal.

SELECT ROUND(3200.8568, 2) ROUND FROM dual;

Null Handling Functions

NVL

NVL stands for Null Value Logic. It handles the NULL values in case we need to replace them with a value. It takes two arguments i.e. NVL(x, y). It returns y if x is NULL. If x is not NULL, x is returned. For example, there are some employees who don’t earn a commission. For those that don’t earn commission, .4 should be given as new commision.

SELECT first_name, commission_pct, NVL(commission_pct, .4) NEW_COMMISSION FROM employees;

NVL2

It works like the NVL. It takes three arguments i.e. NVL(x, y, z). It returns z if x is NULL and returns y if x is NOT NULL. For example, there are some employees who don’t earn a commission. For those that don’t earn commission, .4 should be given as new commission but to those that earn, .3 should be given.

SELECT first_name, commission_pct, NVL2(commission_pct, .3, .4) NEW_COMMISSION FROM employees;

Date Functions

SYSDATE

It doesn’t take any parameter. It returns the current date and time for the operating system of the computer the database resides. Works the same way as CURRENT_DATE fucntion

SELECT SYSDATE FROM dual;

SYSTIMESTAMP

Takes no argument. It returns the current database date and time.

SELECT SYSTIMESTAMP FROM dual;

MONTHS_BETWEEN

It takes two arguments i.e. MONTHS_BETWEEN(x, y). It returns the number of months that exist between y and x dates. Let us determine the months that exist between the current date and the hire date in the employees table.

SELECT first_name, MONTHS_BETWEEN(SYSDATE, hire_date) MONTHS_WORKED FROM employees;

LAST_DAY

Takes one parameter i.e. LAST_DAY (x). It returns the last day of the month for date x.

SELECT LAST_DAY(SYSDATE) LAST_DAY FROM dual;

ADD_MONTHS

It takes two parameters i.e. ADD_MONTHS(x, y). It adds date x to the number of months y to determine the date it will be when date x is added to months y. If y is a negative, an older date will be returned. Example—If we add 7 months to the current date, lets see what the date will be.

SELECT ADD_MONTHS(SYSDATE, 7) FUTURE_DATE FROM dual;

We can’t look at all functions in SQL (Oracle) because they’re too many. We have managed to look at a few of them we think are so important. In case you need more, you can look at all functions in Oracle here.

Summary

1. A function is a program called upon whenever needed.

2. Functions help us in performing tasks with data.

3. A function can take one or more parameters or none.

4. Single row functions work on one row at a time.

5. We have looked at character, number, null handling, and date functions.

6. We have not looked at all functions that exist because they are many. We only focused on a few of them.

7. You can look at conversion functions and more functions in each category.