facebook twitter
Webucator's Free Advanced Oracle SQL Queries Tutorial

Lesson: Scalar SQL Functions (Character)

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.

Lesson Goals

  • Learn what SQL functions are.
  • Learn about character functions.
  • Learn about regular expressions.

What Are SQL Functions?

  • A function is a subprogram that returns a single value.
  • A function can be passed values called parameters or arguments.
  • Functions can be used to perform a variety of tasks including modifying, formatting and calculating values.
  • Oracle includes an extensive library of functions.
  • Additional functions can be created using the PL/SQL programming language.

Character Functions

CONCAT()

The CONCAT() function is used to concatenate (combine) strings. The || operator can also be used to append one string to another.

LENGTH()

The 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:

Code Sample:

SQL-Functions-Character/Demos/function_concat_length.sql
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()

The 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...

  • start the search for the string at a location other than the beginning.
  • search backwards.
  • search for a given occurence of the substring.

REPLACE()

The REPLACE() function is used to substitute one substring for another within a given text string. Oracle also includes a similar function called TRANSLATE() that can be used to perform several single-character, one-to-one substitutions.

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.

Code Sample:

SQL-Functions-Character/Demos/function_instr_replace.sql
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(), LOWER(), and INITCAP()

The UPPER() function is used to convert all of the characters in a string to uppercase.

The LOWER() function is used to convert all of the characters in a string to lowercase.

The 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 UPPER(), LOWER() and INITCAP() on all rows that have last names that start with "McC":

Code Sample:

SQL-Functions-Character/Demos/function_upper_lower_initcap.sql
SELECT last_name,
	UPPER(last_name),
	LOWER(last_name),
	INITCAP(last_name)
FROM employees
WHERE last_name LIKE  'McC%';

LPAD() and RPAD()

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:

Code Sample:

SQL-Functions-Character/Demos/function_rpad_lpad.sql
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: RPAD() and LPAD()

TRIM()

The 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 uses the LENGTH() function to illustrate the differences between the strings.

Code Sample:

SQL-Functions-Character/Demos/function_trim.sql
SELECT 
	LENGTH(txt) AS full_text, 
	LENGTH(TRIM(txt)) AS trimmed_text
FROM
(
	SELECT ' this has  Extra spaces     ' AS txt 
	FROM dual
);

TO_CHAR()

The 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 MM/DD/YYYY format model:

Code Sample:

SQL-Functions-Character/Demos/function_to_char.sql
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()

The 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".

Code Sample:

SQL-Functions-Character/Demos/function_soundex.sql
SELECT first_name 
FROM employees 
WHERE SOUNDEX(first_name) = SOUNDEX('stephen');

Regular Expressions

  • A regular expression is a text string that uses specific characters to describe a search pattern.
  • They are a powerful tool for searching and processing text data in a concise manner.
  • Each regular expression represents a set of possible strings.
  • It consists of character literals and metacharacters.

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 a-z|A-Z or [[:alpha:]].

Parentheses can be used to represent grouping.

Repetition of characters or subpatterns can be characterized by numbers or symbols. For instance, a *> 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 REPLACE(), INSTR() and LIKE.

  • REGEXP_REPLACE()
  • REGEXP_INSTR()
  • REGEXP_LIKE()

The 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:

Code Sample:

SQL-Functions-Character/Demos/regexp_instr.sql
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:

Code Sample:

SQL-Functions-Character/Demos/regexp_like.sql
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 street_address column. 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:

Code Sample:

SQL-Functions-Character/Demos/regexp_replace.sql
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

Character Functions

Duration: 25 to 40 minutes.
  1. Create a query that creates an email address for each employee by concatenating first name and last name (separated by a dot) with @company.com (e.g., "stephen.king@company.com") and give the resulting value the alias "email". The first results of the query should look like this: Results of Query 1
  2. Create a query that uses the query created in the previous step as an inline view, selecting email. Set this query aside as it will be used again in this exercise. The results should be the same as above.
  3. Modify the query created in the previous step to find the length of the longest email address. Next, add a few spaces to the @company.com character string. Now modify the query so that we see the length of the longest email address with and without the spaces that were added. The result should be 29.
  4. Using the inline view created in step two above, change the name of the email domain from @company.com to @newcompany.com. The first results of the query should look like this: Results of Query 4
  5. Create a query that returns the number of employees with an "a" in their first name.

Solution:

SQL-Functions-Character/Solutions/char_function_solutions.sql
-- 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 ;

Challenge

  1. Get a list of all employees who have first names that sound like "daniel". This should return Daniel and Danielle.
  2. Using the query created in step two of the standard exercise above, use a regular expression to reverse the first and last names. The first results of the query should look like this: Results of Challenge 2

Challenge Solution:

SQL-Functions-Character/Solutions/char_function_challenge.sql
--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.\1@company.com') as new_email
FROM
(
	SELECT lower(first_name)||
		'.'||
		lower(last_name)||
		'@company.com' AS email 
	FROM employees
);