SQL concat

Definition and Usage

The CONCAT() function adds two or more expressions together.

Syntax

CONCAT(expression1, expression2, expression3,…)

Question

1. Each company in the accounts table wants to create an email address for each primary_poc. The email address should be the first name of the primary_poc . last name primary_poc @ company name.com.

SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', name, '.com')
FROM (SELECT LEFT(primary_poc, STRPOS(primary_poc, ' ') -1 ) first_name,  
			 RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, name
 	  FROM accounts) t1

ANALYSIS
We split customers' name by first_name and last_name, then we create the columns about customers' first_name, last_name, and their email from the TABLE:ACCOUNTS.

From table 1, we use LEFT, RIGHT, and combined STRPOS to pull the first_name and last_name from from accounts.
Then in table 1, we can use CONCAT to build new expression - customers' emails by using their first_name, last_name, special character and name (Accounts Table). 
i.e.  Tamara	Tuma	Tamara.Tuma@Walmart.com

2. You may have noticed that in the previous solution some of the company names include spaces, which will certainly not work in an email address. See if you can create an email address that will work by removing all of the spaces in the account name, but otherwise your solution should be just as in question 1. Some helpful documentation is here.

select first_name, last_name, concat(first_name, '.', last_name, '@', REPLACE(name, ' ', ''),'.com' )
		from (SELECT LEFT(primary_poc,     STRPOS(primary_poc, ' ') -1 ) first_name,  RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, name
		FROM accounts) t1

ANALYSIS:
Some of the company names include spaces, so we use REPLACE(name, ' ', '') to replace ' ' to ''. REMOVE the Spaces 
For example, 
Sung	Shields	Sung.Shields@Exxon Mobil.com
will be
Sung	Shields	Sung.Shields@ExxonMobil.com

3. We would also like to create an initial password, which they will change after their first log in. The first password will be the first letter of the primary_poc’s first name (lowercase), then the last letter of their first name (lowercase), the first letter of their last name (lowercase), the last letter of their last name (lowercase), the number of letters in their first name, the number of letters in their last name, and then the name of the company they are working with, all capitalized with no spaces.

SELECT first_name, last_name, concat(first_name, '.', last_name, '@', REPLACE(name, ' ', ''),'.com' ), LEFT(LOWER(first_name), 1) || RIGHT(LOWER(first_name), 1) || LEFT(LOWER(last_name), 1) || RIGHT(LOWER(last_name), 1) || LENGTH(first_name) || LENGTH(last_name) || REPLACE(UPPER(name), ' ', '')
FROM (SELECT LEFT(primary_poc,     STRPOS(primary_poc, ' ') -1 ) first_name,  RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, name FROM accounts)t1;

ANALYSIS:
We will create an initial password. 
The first password will be the first letter of the primary_poc's first name (lowercase), then the last letter of their first name (lowercase), 
the first letter of their last name (lowercase), the last letter of their last name (lowercase), the number of letters in their first name, 
the number of letters in their last name, and then the name of the company they are working with, all capitalized with no spaces.
i.e.
Tamara	Tuma	Tamara.Tuma@Walmart.com	tata64WALMART
LEFT(LOWER(first_name), 1)         => t
RIGHT(LOWER(first_name), 1)       => a
LEFT(LOWER(last_name), 1)         => t
RIGHT(LOWER(last_name), 1)       => a
LENGTH(first_name)                       => 6
LENGTH(last_name)                        => 4
REPLACE(UPPER(name), ' ', '')       =>WALMART 

Sung	Shields	Sung.Shields@ExxonMobil.com	 sgss47EXXONMOBIL
REPLACE(UPPER(name), ' ', '')       =>ExxonMobil