Combining functions for string manipulation
In the next example, we are going to break apart the email
column from the customer
table into three new derived fields. Parsing a single column into multiple columns can be useful when you need to work with certain subsets of data. Email addresses have embedded information stored in them that can be parsed out to derive additional information about our data. For example, we can use the techniques we learned about in the video to determine how many of our customers use an email from a specific domain.
This exercise is part of the course
Functions for Manipulating Data in PostgreSQL
Exercise instructions
- Extract the characters to the left of the
@
of theemail
column in thecustomer
table and alias it asusername
. - Now use
SUBSTRING
to extract the characters after the@
of theemail
column and alias the new derived field asdomain
.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
-- Extract the characters to the left of the '@'
___(___, ___('@' IN email)-1) AS username,
-- Extract the characters to the right of the '@'
___(___ FROM ___('@' IN email)+1 ___ ___(___)) AS domain
FROM customer;