[Udemy] 15 Days of SQL - Day 4 ① : Functions(LENGTH, LOWER, UPPER, LEFT, RIGHT), Concatenate, POSITION, SUBSTRING

1. LOWER & UPPER, LENGTH

• Syntax

SELECT
LOWER (email) AS email_lower,
email,
LENGTH(email)
FROM customer
WHERE LENGTH(email) < 30

 

* Challenge

Write a query to find customers whose either the first name or the last name is more than 10 characters long.

Output the list of these first and last names in all lower case.

SELECT
LOWER(first_name),
LOWER(last_name),
LOWER(email)
FROM customer
WHERE LENGTH(first_name)>10 or LENGTH(last_name)>10

---

2. LEFT & RIGHT

• Extract particular letters
• Syntax

SELECT
LEFT(first_name, 2), -- from the 'first_name' column, extract the left 2 letters
first_name
FROM customer

 

SELECT
RIGHT(LEFT(first_name, 2),1), -- getting only the second letter by nesting with RIGHT
first_name
FROM customer

 

* Challenge

Write a query to extract the last 5 characters of the email address.

The email address always ends with '.org' and extract just the dot '.' from the email address.

SELECT
RIGHT(email, 5) as ends,
RIGHT(LEFT(RIGHT(email, 5), 2), 1)
--LEFT(RIGHT(email, 4), 1)
FROM customer

 

---

3. Concatenate

• Combine different infomation from multiple strings and bring them together in one column
• ||
• Syntax

SELECT
LEFT(first_name, 1) || '.' || LEFT(last_name, 1) || '.' AS initials,
first_name,
last_name
FROM customer

 

 

* Challenge

Write a query to create an anonymized version of the email address.

It should be the first character followed by '***' and then the last part starting with '@'.

The email address always ends with '@sakilacustoemr.org'.

SELECT
LEFT(first_name, 1) || '***' || '@sakilacustomer.org' AS anonymous
FROM customer

---

4. POSITION

• Specify what type of strings we are searching and get the position of where it is
• Syntax

SELECT
LEFT(email, POSITION('@' IN email)-1), -- place where '@' is
email
FROM customer

 

* Challenge

Write a query to extract the first name from the email address and concatenate it with the last name in the form : 'Last name, First name.'

SELECT
last_name || ',' || LEFT(email, POSITION('.' IN email)-1),
last_name
FROM customer

---

5. SUBSTRING

• Used to extract a substring from a string
• Used with POSITION
• Syntax

SUBSTRING (string from start [for length])
-- string : column or string that we want to extract from
-- start : position, where to start from
-- length : for how many characters, if not specified - the rest of the string will be come out)

SUBSTRING (email from 2 for 3)
SUBSTRING (email from POSITION ('.' in email) for 3)

 

 

* Instead of LENGTH, use POSITION 

SELECT
email,
SUBSTRING (email from POSITION ('.' in email)+1 for POSITION('@' in email)-POSITION('.' in email)-1)
FROM customer

 

* Challenge

1. Write a query to create an anonymized form of the email addresses in the following way : M***.S***@sakilacustomer.org

SELECT
LEFT(first_name, 1) || '***.' || LEFT(last_name, 1) || '***@sakilacustomer.org'
FROM customer

 

2. Write a query to create an anonymized form of the email addresses in the following way :

***M.S***@sakilacustomer.org (last character from the first name,'.', first character form the last name)

SELECT
'***'
|| SUBSTRING(email from POSITION('.' in email)-1 for 3)
|| '***'
|| SUBSTRING(email from POSITION('@'in email))
FROM customer