# Character Functions - Exercise

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

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