facebook google plus 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 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.

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) IN 
(
	SELECT MAX(LENGTH(first_name|| last_name)) 
	FROM employees
);

This 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 (including the length of both the first and last name for the record).

INSTR

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

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;

This 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 retrns the original first name field for comparison.

UPPER

The UPPER function is used to convert all of the character in a string to upper case.

LOWER

The LOWER function is used to convert all of the character in a string to lower case.

INITICAP

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.

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%';

This demo illustrates the effects of UPPER, LOWER and INITCAP on all rows that have last names that start with "McC".

LPAD

LPAD is used to return a given string left-padded to a given length. The characters used for padding can be specified as well.

RPAD

RPAD provides the same basic functionality as rpad, but performs right rather than left padding.

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;

This query illustrates the effects of right and left padding names in the employee table to a length of 7 characters with dashes. Notice the effect of these functions on first names that are longer than seven characters.

TRIM

The TRIM function is used to remove leading and/or trailing white space characters from a string.

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
);

This query shows the effects of stripping white space from a string USING the TRIM function. It uses the LENGTH function to illustrate the differences between the strings.

TO_CHAR

The TO_CHAR function is used to cast date, numeric or character data to a character type string. A variety of format models are available to change the appearance of numeric and date data.

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;

This query demonstrates how TO_CHAR can be used to structure numeric data as currency and date data using the MM/DD/YYYY format model. To see a list of formats available to go: TO_CHAR Functions

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 algorithm for this function was devised by Donald E. Knuth in his book "The Art of Computer Programming, Volume 3: Sorting and Searching".

Code Sample:

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

This query returns rows where the first name of the employee sounds like "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:]].

Parenthesis can be used to represent grouping.

Repitition of characters or subpatterns can be characterized by numbers or symbols. For instance, a * represents zero or more occurences of a string.

Groups that are matched can be refenced 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 counterparts REPLACE, INSTR and LIKE.

  • REGEXP_REPLACE
  • REGEXP_INSTR
  • REGEXP_LIKE

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 locations' table street_address column contains relatively unstructured data which is often addressed effectively using regular expressions. The first query returns rows where the street address does not contain a digit between zero and nine. The second query returns locations with the word "Street" or the abbreviation "St".

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:]]$');

Regular expressions can be useful for describing strings to match in a terse manner. The first query illustrates how one might use a series of LIKE clauses to find all columns that contain either a dash, left-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.

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;

These two queries use the REGEXP_REPLACE function to effectively obscure some portion of the data returned. 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 go to : Regular Expressions

Character Functions

Duration: 15 to 20 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.
  2. Create a query that uses the query created in the previous step as an inline view. Set this query aside as it will be used again in this exercise.
  3. First, 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.
  4. Start again with the query created in step two above. In the outer query, change the name of the email domain from @company.com to @newcompany.com.
  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.' ) 
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"
  2. Using the query created in step two above, use a regular expression to reverse the first and last names.

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')
FROM
(
	SELECT lower(first_name)||
		'.'||
		lower(last_name)||
		'@company.com' AS email 
	FROM employees
);