How do I create a list of emails from first name, last name and city?

Question

To clarify the question: I have a table with customers which includes the first_name and last_name and the address_id. Now I have to create a list of new emails which are made of the first name and last name and the city: firstname.lastname@city.com I am using the sakila database btw if that is of any help Could anybody explain to me, how I can accomplish this? Thanks in advance!

LukStorms · Accepted Answer · 2021-12-25T22:36:43.130

1

The customer table has the first and last name, but not the city.
It has an address_id though, so we can join to the address table.

The address table doesn't have the city name either, but it has a city_id.
So we join the city table to the address table.

Now with the required parts available, the new email can be constructed.

select replace(
          lower(
             concat(cust.first_name, '.', cust.last_name, '@', city.city, '.com')
          ), ' ', '_') as new_email
from customer cust
join address addr
  on addr.address_id = cust.address_id
join city
  on city.city_id = addr.city_id
order by new_email;

Some cities have spaces, so they were replaced.

edited Dec 25 '21 at 22:36

answered Dec 25 '21 at 22:26

LukStorms

28,916
5
31
45

That's it! It works and makes sense, I really appreciate the help! I'm currently working on sql quereys, do you know if there is like a discord server for questions because I'd say my question was quite easy and I don't want to spam stack with such "easy" questions. And thanks for the help – Leo Dec 25 '21 at 22:39
Sorry, I never used discord for such things. But wouldn't be surprised if there were. Well, the internet doesn't lack information about Sql. – LukStorms Dec 25 '21 at 22:41
when seeing `lower(...`, i wanted to point to this question/answer: [Are email addresses case sensitive?](https://stackoverflow.com/questions/9807909/are-email-addresses-case-sensitive) – Luuk Dec 26 '21 at 10:44
1

@Luuk From what I understand, [the domain name is case-insensitive by convention](https://www.ietf.org/rfc/rfc1034.txt). But the rest, that depends on the mailing system. Most probably treat it case-insensitive though. – LukStorms Dec 26 '21 at 11:11

score 1 · Answer 2 · answered Dec 25 '21 at 22:31

You can use mysql concat function to concatenate those columns into an email.

concat(first_name, '.', last_name, '@', city, '.com')

You also need to remove whitespaces from all involved column to generate valid emails using the replace function.

Knowing this, just join the three tables customer, address and city to get all the needed columns.

select first_name, last_name, 
concat(REPLACE(first_name, ' ', ''), '.', REPLACE(last_name, ' ', ''), '@', REPLACE(city, ' ', ''), '.com') as email
from customer c inner join address a on c.address_id = a.address_id
inner join city ct on a.city_id = ct.city_id

Fiddle

How do I create a list of emails from first name, last name and city?

2 Answers2