Given the following customer data:
Id | Email
1 | john.jones@xmail.com
2 | tracey.smith@newmail.com
3 | amy.sanders@royalmail.com
4 | rachel@gmail.com
Create firstname and lastname columns with each value capitalized.

Question

Assisting AI · Accepted Answer

You can extract the name parts from the email using string functions and then capitalize them. In MySQL:
```sql
SELECT
  id,
  email,
  INITCAP(SUBSTRING_INDEX(email, '.', 1)) AS firstname,
  INITCAP(SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', 1), '.', -1)) AS lastname
FROM customers;
```
`SUBSTRING_INDEX(email, '.', 1)` grabs the part before the first dot (first name). `SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', 1), '.', -1)` first removes the domain, then grabs the part after the last dot (last name). `INITCAP` capitalizes the first letter of each word. If `INITCAP` is not available, use `UPPER(LEFT(col,1)) || LOWER(SUBSTRING(col,2))` in PostgreSQL or `CONCAT(UPPER(SUBSTRING(col,1,1)), LOWER(SUBSTRING(col,2)))` in other dialects.

Given the following customer data: Id | Email 1 | john.jones@xmail.com 2 | tracey.smith@newmail.com 3 | amy.sanders@royalmail.com 4 | rachel@gmail.com Create firstname and lastname columns with each value capitalized.

💡 Model Answer

🎤 Get questions like this answered in real-time