Skip to content Skip to main navigation Skip to footer

Case and Character Manipulation

Objectives

This tutorial covers the following objectives:

  • Select and apply single-row functions that perform case conversion and/or character manipulation
  • Select and apply character case-manipulation functions LOWER, UPPER, and INITCAP in a SQL query
  • Select and apply character-manipulation functions CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM, and REPLACE in a SQL query
  • Write flexible queries using substitution variables

Purpose

  • Being able to change the way in which data is presented is important when dealing with data from a database.
  • Most of the time in SQL, we need to change the way that data appears depending on the requirements of the task we are trying to accomplish.
  • In this lesson, you will learn several ways in which to transform data to fit a particular situation.

DUAL Table

  • The DUAL table has one row called “X” and one column called “DUMMY.”
DUAL Table
  • The DUAL table is used to create SELECT statements and execute functions not directly related to a specific database table.
  • Queries using the DUAL table return one row as a result. DUAL can be useful to do calculations and also to evaluate expressions that are not derived from a table.
  • DUAL will be used to learn many of the single-row functions.
  • In this example the DUAL table is used to execute a SELECT statement that contains a calculation.
  • As you can see the SELECT statement returns a value that does not exist in the DUAL table.
  • The value returned is a result of the calculation executed.
SELECT (319/29) + 12
FROM DUAL;
DUAL Table

Single-Row Character Functions

  • Single-row Character Functions are divided into two categories:
    • Functions that convert the case of character strings
    • Functions that can join, extract, show, find, pad, and trim character strings
  • Single-row functions can be used in the SELECT, WHERE, and ORDER BY clauses.
  • Case-manipulation functions are important because you may not always know in which case (upper, lower, or mixed) the data is stored in the database.
  • Case manipulation allows you to temporarily convert the database data to a case of your choosing.
  • Mismatches between database case storage and query case requests are avoided.

Case Manipulation Functions

  • Case-manipulation functions are used to convert data from the state it is stored in a table to lower, upper or mixed case.
  • These conversions can be used to format the output and can also be used to search for specific strings.
  • Case-manipulation functions can be used in most parts of a SQL statement.
Case Manipulation Functions
  • Case-manipulation functions are often helpful when you are searching for data and you do not know whether the data you are looking for is in upper or lower case.
  • From the point of view of the database, ‘V’ and ‘v’ are NOT the same character and, as such, you need to search using the correct case.
  • LOWER(column | expression) converts alpha characters to lower-case.
SELECT last_name
FROM employees
WHERE LOWER(last_name) = 'abel';

The reason Oracle differentiates between ‘V’ and ‘v’ is due to the way it stores characters. It does not store the chars directly, but their corresponding binary values, depending on the character set of the database.

In most of the Western world, an ACSII character set will have been used as the database character set, and the binary codes for ‘V’ and ‘v’ are different numbers; therefore, Oracle does not consider them to be equal.

  • UPPER(column | expression) converts alpha characters to upper-case.
SELECT last_name
FROM empl oyees
WHERE UPPER(last_name) = 'ABEL';
  • INITCAP(column | expression) converts alpha character values to uppercase for the first letter of each word.
SELECT last_name
FROM employees
WHERE INITCAP(last_name) = 'Abel';

Using Case Manipulation Functions in the WHERE clause allows you to retrieve rows regardless of the case they are stored in the table.

Using Case Manipulation Functions in the SELECT clause alters the way in which the results of the query are displayed.

Character Manipulation Functions

  • Character-manipulation functions are used to extract, change, format, or alter in some way a character string.
  • One or more characters or words are passed into the function and the function will then perform its functionality on the input character strings and return the changed, extracted, counted, or altered value.

CONCAT: Joins two values together

  • Takes 2 character string arguments, and joins the second string to the first. could also be written using the concatenation operator – ‘Hello’ || ‘World’
Character Manipulation Functions

SUBSTR: Extracts a string of a determined length

  • The arguments are (character String, starting position, length).
  • The Length argument is optional, and if omitted, returns all characters to the end of the string.
Character Manipulation Functions
  • Example 1: extracts a substring of 5 characters from position 1 of ‘HelloWorld’.
  • Example 2: extracts a substring starting at position 6 of ‘HelloWorld’ to the end of the string.
  • Example 3: extracts a substring of the first 3 characters from employee last names.

LENGTH: Shows the length of a string as a number value

  • The function takes a character string as an argument, and returns the number of characters in that character string.
Character Manipulation Functions
  • The second example returns the number of characters in each employees last name.

INSTR: Finds the numeric position of the specified character(s)

  • INSTR searches for the first occurrence of a substring within a character string and returns the position as a number.
  • If the substring is not found, the number zero is returned.
Character Manipulation Functions
  • Example 1: a ‘W’ is the 6th character of the first string argument, so the function would return the number 6.
  • Example 2: returns the first occurrence of the character ‘a’ in employee last names. If the name does not contain ‘a’, zero is returned. Even though Abel contains an “A”, it is in the wrong case, hence the 0 is returned.

LPAD: Pads the left side of a character string, resulting in a right-justified value

  • LPAD requires 3 arguments: a character string, the total number of characters in the padded string, and the character to pad with.
Character Manipulation Functions
  • Example 1: the string ‘HelloWorld’ is left padded to 15 characters using the ‘-‘ symbol. As the string has a length of 10 characters, 5 ‘-‘ symbols are added to the left. .
  • Example 2: Employee last names are left padded to 10 characters using an ‘*’

RPAD: Pads the right-hand side of a character string, resulting in a left-justified value

Character Manipulation Functions

TRIM: Removes all specified characters from either the beginning, the end, or both beginning & end of a string

  • The syntax for the trim function is:
Character Manipulation Functions
  • Example 1: removes the leading ‘a’ from the start of the string ‘abcba’
  • Example 2: removes the trailing ‘a’ from the end of the string ‘abcba’
  • Example 3: removes both the leading ‘a’ and the trailing ‘a’ from the string ‘abcba’.
  • If LEADING, TRAILING or BOTH are omitted, the function returns BOTH.
  • IF the specified character is not the leading (or trailing) character of the string, it is not trimmed, for example TRIM (LEADING ‘a’ FROM ‘xyz’) would return ‘xyz’

REPLACE: Replaces a sequence of characters in a string with another set of characters

  • The syntax for the REPLACE function is:
    • string1 is the string that will have characters replaced in it
    • string_to_replace is the string that will be searched for and taken out of string1
    • [replacement_string] is the new string to be inserted in string1
REPLACE (string1, string_to_replace, [replacement_string] ) 
Character Manipulation Functions
  • Example 1: Every instance of ‘J’ in the string ‘JACK and JUE’ is replaced with ‘BL’.
  • Example 2: If the replacement string argument is omitted, the string_to_replace is deleted, So every instance of ‘J’ in the string ‘JACK and JUE’ is removed.
  • Example 3: Every instance of the character ‘a’ in employee last names is replaced with a ‘*’ character.

Using Column Aliases With Functions

  • All functions operate on values that are in parentheses, and each function name denotes its purpose, which is helpful to remember when constructing a query.
  • Often a column alias is used to name a function.
  • When a column alias is used, the column alias appears in the output instead of the actual function syntax.
  • In the following examples, the alias “User Name” has replaced the function syntax in the first query.
  • By default, the column name in a SELECT statement appears as the column heading.
  • In the second query example, however, there is no column in the table for the results produced, so the query syntax is used instead.
SELECT LOWER(last_name)|| LOWER(SUBSTR(first_name,1,1))
AS "User Name"
FROM employees;
Using Column Aliases With Functions
SELECT LOWER (last_name)||LOWER(SUBSTR(first_name,1,1))
FROM f_staffs;
Using Column Aliases With Functions

The first example uses an Alias for the column heading, and is more readable and user-friendly than the second example which has no column alias.

Substitution Variables

  • Occasionally you may need to run the same query with many different values to get different result sets.
  • Imagine for instance if you had to write a report of employees and their departments, but the query must only return data for one department at a time.
  • Without the use of substitution variables, this request would mean you would have to repeatedly edit the same statement to change the WHERE-clause.
  • Luckily for us, Oracle Application Express supports substitution variables.
  • To use them, all you have to do is replace the hardcoded value in your statement with a :named_variable.
  • Oracle Application Express will then ask you for a value when you execute your statement.
  • If this was the original query:
SELECT first_name, last_name, salary, department_id
FROM employees
WHERE department_id= 10;

Then run it again with different values: 20, 30, 40… etc.

  • It could be re-written as:
SELECT first_name, last_name, salary, department_id
FROM employees
WHERE department_id=:enter_dept_id;
  • Note the use of : in front of enter_dept_id.
  • It is the colon that is the magic bit and makes Oracle Application Express recognize the text that follows as a variable.

A Substitution Variable is User-Defined at the time of execution.

  • When you click Run, a pop-up like the following is displayed by Oracle Application Express:
Substitution Variables
  • NOTE: Pop-Up blockers must be disabled, otherwise APEX cannot ask for the variable value, as this is entered via a pop-up.
  • Substitution variables are treated as character strings in Oracle Application Express, which means that when passing in character or date values, you do not need the single quotation marks that you would normally use to enclose the strings.
  • So a WHERE-clause would look like this:
SELECT *
FROM employees
WHERE last_name = :l_name;

Terminology

Key terms used in this tutorial included:

  • Character functions
  • CONCAT
  • DUAL
  • Expression
  • Format
  • INITCAP
  • Input
  • INSTR
  • LENGTH
  • LOWER
  • LPAD
  • Output
  • REPLACE
  • RPAD
  • Single-row functions
  • SUBSTR
  • TRIM
  • UPPER
  • Substitution variable

Summary

In this tutorial, you should have learned how to:

  • Select and apply single-row functions that perform case conversion and/or character manipulation
  • Select and apply character case-manipulation functions LOWER, UPPER, and INITCAP in a SQL query
  • Select and apply character-manipulation functions CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM, and REPLACE in a SQL query
  • Write flexible queries using substitution variables

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *