Single-Row Functions

Introduction

Functions are simply a database object that processes values from the input and produces a new result. The figure below illustrates the definition:

Single-row functions are functions that accept a single value and returns a single result. It can also accept two or more values to allow creation of various outputs.

These functions are often used in SELECT statements to alter the set of records, which some of them is useful for organizing the data.

List of Single-Row Functions:

• LOWER

• UPPER

• SUBSTR

• CONCAT

• LENGTH

• INSTR

• LPAD

• RPAD

• TRIM

• LTRIM

• RTRIM

• ASCII

The single-row functions listed above are the functions used for string manipulation or acquisition of string attributes.

Testing a Function

Exploring various functions is helpful for familiarizing the purpose of the functions. To use mere arbitrary values instead of everything from the columns, insert those values to a chosen function and instead of selecting from the table, select from DUAL. Here is the following example:

SELECT UPPER('present') FROM DUAL;

Converting a String to Uppercase or Lowercase

To convert any string into uppercase or lowercase, functions UPPER and LOWER are used to process the conversion.

SELECT UPPER(name), LOWER(name) FROM users;

These functions convert the entire column to uppercase and lowercase, processing each value in each function. It only affects alphabetical characters (letters in an alphabet).

UPPER:

• Input: Robert

• Output: ROBERT

LOWER:

• Input: Robert

• Output: robert

Obtaining a Substring

A substring is a smaller part of a string extracted from it, i.e., a fragment of a string.

SELECT SUBSTR(name, 1, 1) FROM users;

This shows all the first letters of their names in the table. The first parameter (name) of a function simply takes a string to obtain a substring from. The second parameter (first 1) takes a numeric value to specify the position of the substring, similar to the third parameter (second 1), but it is used to specify the length of it. 

SUBSTR('Robert', 2, 3):

• Input: Robert

• Output: obe

The following image below illustrates the process of obtaining a substring from a string:

Additionally, when zero is passed at the second parameter, it merely starts at the first character, no difference between that and one. Thus, SUBSTR('Clark', 0, 3) has the same output as SUBSTR('Clark', 1, 3), which is Cla.

It returns null if the numerical position is beyond the string, e.g., SUBSTR('Pie', 4, 2). Pie has only three characters, but the position argument is 4 which is out of range within the string. It also returns null if the substring length passed is zero, e.g., SUBSTR('Pie', 2, 0).

Concatenating Strings and Determining the Length of a String

Concatenation is a process of combining two or more values. The following example combines the first letter of a name with a period:

SELECT CONCAT(SUBSTR(name, 1, 1), '.') FROM users;

CONCAT function only takes two string arguments; it doesn't take more than two. You have to use multiple CONCAT functions to achieve concatenation of multiple strings.

CONCAT('Oracle', ' Database'):

• Input: strings 'Oracle' and ' Database'

• Output: Oracle Database

Another example below determines the length (number of characters in a string) of each name in the table: 

SELECT LENGTH(name) FROM users;

LENGTH('Oracle Database'):

• Input: Oracle Database

• Output: 15 (space is included as a character)

Padding Strings

Padding is a process of appending few or several characters to a string until a desired number of characters is reached. The following example pads a couple of names with asterisks from the right until it has 20 characters each name:

SELECT RPAD(name, 20, '*') FROM users;

Both LPAD and RPAD takes three arguments: a string value (commonly a column to capture values within it), maximum length, and a padding character. Ensure these arguments are arranged in order. 

LPAD('Oracle', 10, '-'):

• Input: Oracle

• Output: ----Oracle

RPAD('Oracle', 12, '~'):

• Input: Oracle

• Output: Oracle~~~~~~

The figures below illustrate the differences between the two functions and their shared purposes:

Trimming Characters from a String

Trimming is the process of eliminating, commonly, leading or trailing (or both) whitespaces or specific characters from a string. There are multiple ways to use the TRIM function:

TRIM('  Java ') – Trimming whitespaces from both ends

TRIM('*' FROM '******Java***') – Trimming characters that match the specific character from both ends

TRIM(BOTH '*' FROM '******Java***') – Same as above

TRIM(LEADING '-' FROM '----Java------') – Trimming leading characters that match the specific character

TRIM(TRAILING '-' FROM '----Java------') – Trimming trailing characters that match the specific character

TRIM(BOTH FROM '    Java  ') – Same as very first

TRIM(LEADING FROM '    Java  ') – Trimming leading whitespaces

TRIM(TRAILING FROM '    Java  ') – Trimming trailing whitespaces

The figure below shows the leading and trailing characters in a string:

Functions LTRIM and RTRIM usually seem self-explanatory if you fully understand the main TRIM function. LTRIM just trims leading whitespaces or repeating single characters. Same with RTRIM, but it does at the right end (trailing). These functions take only two arguments: a string value and a character to trim, e.g., LTRIM('::::Trim:::', ':'), this example eliminates the leading colons from a string. If a string value is only specified, it will then eliminate whitespaces from the left (leading) or right (trailing), e.g., RTRIM('  Trim       ').

Finding the Index of a Substring from a String

Each character has its own index to indicate its position in a string. In programming, array indices start from 0 and ends with the length of an array minus one. While SQL string indices start from 1 and ends with the length of a string.

The following example searches a bunch of names that contain a substring "ty":

SELECT INSTR(name, 'ty') FROM users;

Take note that INSTR returns an integer value—a position value where the substring is found, otherwise 0—not a boolean value.

The figure below illustrates the process of the INSTR function. The starting point of the search is determined by the third parameter (1). The said parameter can be omitted to begin the search with the first character of a string, e.g., INSTR(NAME, 'll'). The source string—or a column of strings—is determined by the first parameter (NAME), and the substring to search is determined by the second parameter (ll). Anything inside the preceding parentheses are the arguments for the parameters, you might think these are "parameters".

If a substring is found within the string, the numerical position is then returned where the substring is found, otherwise 0 is returned. 

ASCII Function

This function is basically used to determine the ASCII code of the first character of a string. Have this following lengthy example as a reference:

SELECT

  SUBSTR(name,1,1) AS first_letter,

  ASCII(name) - 64 AS letter_pos

FROM users;

Overall, this query displays two columns: the first letter from a name and its corresponding letter position in the alphabet. The display column names are changed using the AS keyword to make it meaningful.

The first letter of a name is obtained by calling SUBSTR(name,1,1), which merely extracts a single-character substring from the leftmost side of the string.

The letter position in the alphabet is obtained by using the ASCII function, then diminishing it by 64. Uppercase letters start with 65—which is A—so by diminishing it by 64, we can then obtain its position in the alphabet. To know more about the ASCII of various characters, visit this link.