Character Functions - Exercise

Contact Us or call 1-877-932-8228
Character Functions - Exercise

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