Welcome to our free Advanced Oracle SQL Queries tutorial. This tutorial is based on Webucator's Advanced Oracle SQL Queries course.
SQL Functions return values based upon specified input. They provide functionality that is traditionally addressed in a procedural rather than a set-oriented programming paradigm. This lesson will provide an overview of functions included with Oracle that relate to character data.
CONCAT() function is used to concatenate (combine) strings.
|| operator can also be used to append one string to another.
LENGTH() function returns the number of
characters (including spaces and hidden characters) in the
string passed to it.
The following query demonstrates the use of the concatenation operator
||) as well as the
LENGTH() function. It returns the rows with
the longest combined first name and last name and outputs the first name and last name and the length of each:
SELECT first_name, last_name, LENGTH(first_name), LENGTH(last_name) FROM employees WHERE LENGTH(first_name||last_name) = ( SELECT MAX(LENGTH(first_name||last_name)) FROM employees );
INSTR() (for "IN STRing") function is used to determine if a given string contains
a specified substring. The function returns an integer representing
the position of the substring (if found). If the substring is not
found, zero is returned. There are variations of the function that
can be used to...
REPLACE() function is used to substitute one substring for another
within a given text string. Oracle also includes a similar function
TRANSLATE() that can be used to perform several single-character,
The following query returns all rows where the first name
includes the substring "an" as part of the name.
The columns in the
SELECT clause reveal where the substring was
found (its position), replaces the string with a capitalized
version of the search string ("AN"), and also
returns the original
first_name field for comparison.
SELECT INSTR(first_name, 'an') AS position, REPLACE(first_name,'an','AN') AS first_name_with_an, first_name FROM employees WHERE INSTR(first_name, 'an') > 0;
UPPER() function is used to convert all of the characters
in a string to uppercase.
LOWER() function is used to convert all of the characters
in a string to lowercase.
INITCAP() function is used to capitalize the first character
in each substring that appears as an independent word in the string.
It sets all remaining characters to lower case.
The following demo illustrates the effects of
on all rows that have last names that start with "McC":
SELECT last_name, UPPER(last_name), LOWER(last_name), INITCAP(last_name) FROM employees WHERE last_name LIKE 'McC%';
LPAD() is used to return a given string left-padded to a given length.
By default, spaces are used for padding, but that can be specified as well.
RPAD() provides the same basic functionality as
LPAD(), but performs right rather than left padding.
The following query illustrates the effects of right and left padding names in the employee table to a length of seven characters with dashes:
SELECT RPAD(first_name,7,'-') AS r, LPAD(first_name,7,'-') AS l, first_name FROM employees;
Notice the effect of these functions on first names that are longer than seven characters:
TRIM() function is used to remove leading and/or trailing
whitespace characters from a string.
The following query shows the effects of stripping whitespace from
a string using the
TRIM() function. It
LENGTH() function to illustrate the differences between
SELECT LENGTH(txt) AS full_text, LENGTH(TRIM(txt)) AS trimmed_text FROM ( SELECT ' this has Extra spaces ' AS txt FROM dual );
TO_CHAR() function is used to cast date, numeric or character
data to a string type. A variety of format models
are available to change the appearance of numeric and date data.
The following query demonstrates how
TO_CHAR() can be used to structure
numeric data as currency and date data using the
SELECT first_name, last_name, TO_CHAR(salary,'$999,999.00') AS salary, TO_CHAR(hire_date, 'MM/DD/YYYY') AS hire_date FROM employees;
For more information on
TO_CHAR() format models, visit: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Format-Models.html.
SOUNDEX() function returns a character string containing a
phonetic representation that can be compared with other strings.
Words that are spelled differently but sound alike (in English)
will return the same value.
The following query returns rows where the first name of the employee sounds like "stephen".
SELECT first_name FROM employees WHERE SOUNDEX(first_name) = SOUNDEX('stephen');
There are a number of special characters that can be used when constructing a regular expression.
The letters from a to z can be represented as
Parentheses can be used to represent grouping.
Repetition of characters or subpatterns
can be characterized by numbers or symbols. For instance,
*> represents zero or more occurrences of a character or group of characters.
Groups that are matched can be referenced by their position
\1 for instance) which is useful when used in conjunction with
replacing groups within strings.
The three regular expression functions perform in a similar manner
to their string counterparts
street_address column in the
locations table contains
relatively unstructured data which is often addressed effectively
using regular expressions. Take a look at the following queries:
SELECT * FROM locations WHERE REGEXP_INSTR(street_address,'[0-9]') = 0; SELECT * FROM locations WHERE REGEXP_INSTR(street_address,'Street| St') > 0;
The first query returns rows where the street address does not contain a digit between 0 and 9. The second query returns locations with the word "Street" or the abbreviation "St".
Regular expressions can be useful for describing strings to match in a terse manner. Take a look at the following queries:
SELECT * FROM locations WHERE street_address LIKE '%-%' OR street_address LIKE '%(%' OR street_address LIKE '%,%'; SELECT * FROM locations WHERE REGEXP_LIKE(street_address,'-|\(|,'); SELECT * FROM locations WHERE REGEXP_LIKE(street_address,'[[:digit:]]$');
The first query illustrates how one
might use a series of
LIKE clauses to find all columns that
contain either a dash, open parenthesis or a comma. The next
query returns the same rows, but is much shorter. The pipe (
is used for alternation, and the backslash (
\) is required as
an escape sequence. The last query returns records that have
a digit as the last character in the
$ indicates the string must end there.
The following two queries use the
REGEXP_REPLACE() function to effectively
obscure some portion of the data returned:
SELECT REGEXP_REPLACE(street_address, '[[:alpha:]]', 'x') AS result FROM locations; SELECT REGEXP_REPLACE(phone_number, '[[:digit:]]', '9') AS result FROM employees;
In the first query, all alpha characters in the street_address are replaced by x's. In the second query, all numeric characters in the phone number are replaced with nines.
For a listing of Oracle Regular Expressions visit https://docs.oracle.com/cd/B13789_01/appdev.101/b10795/adfns_re.htm#1006826
-- Email Addresses SELECT lower(first_name)|| '.'|| lower(last_name)|| '@company.com' AS email FROM employees; --Inline View SELECT email FROM ( SELECT lower(first_name)|| '.'|| lower(last_name)|| '@company.com' AS email FROM employees ); --Longest Email Address SELECT max(length(email)) FROM ( SELECT lower(first_name)|| '.'|| lower(last_name)|| '@company.com' AS email FROM employees ); --Added spaces, trimmed and untrimmed length SELECT max(length(trim(email))), max(length(email)) FROM ( SELECT lower(first_name)|| '.'|| lower(last_name)|| '@company.com ' AS email FROM employees ); --Changed the company name SELECT replace(email,'@company.','@newcompany.' ) as new_email FROM ( SELECT lower(first_name)|| '.'|| lower(last_name)|| '@company.com' AS email FROM employees ); --Count of employee first names that have an a in them: SELECT count(*) FROM employees WHERE instr(upper(first_name),'A') > 0 ;
--Employees with first names that sound like daniel SELECT first_name FROM employees WHERE soundex(first_name) = soundex('daniel'); --Use of a regular expression to reverse first and last names SELECT email, REGEXP_REPLACE(email, '([[:alpha:]]*).([[:alpha:]]*)@company.com', '\2.\firstname.lastname@example.org') as new_email FROM ( SELECT lower(first_name)|| '.'|| lower(last_name)|| '@company.com' AS email FROM employees );