MySQL Email Filtering-CodePudding

Hey guys I'm stuck trying to figure out the code to this question on MySQL as an HR

The company email naming standard is to concatenate the first character of the first name with the last name (for example SKING for Steven King) Write a select statement that returns the employee ID, first name, last name and email of the employees whose email does not follow the company standard

SELECT EMPLOYEE_ID, FIRST_NAME, Last_Name, EMAIL FROM EMPLOYEES WHERE EMAIL NOT SUBSTRING(FIRST_NAME, 1);

I get an error of: Incorrect syntax near 'SUBSTRING'.

Thanks for anyone helping me with this one.

CodePudding user response：

I think you have to add the 3rd parameter to SUBSTRING(string, starting_position, num_of_characters)? Since you are only wanting the first character to match?

SUBSTRING(FIRST_NAME,1,1);

https://www.w3schools.com/sql/func_mysql_substring.asp

SELECT EMPLOYEE_ID, FIRST_NAME, Last_Name, EMAIL
FROM EMPLOYEES 
WHERE EMAIL <> CONCAT(SUBSTRING(FIRST_NAME,1,1), Last_Name, '@domain.com')

CodePudding user response：

Above query is giving an error because Substring needs one more parameter as:

SUBSTRING(FIRST_NAME,1,1);

But i think above query will not even give the right answer because it will just check first character of First name in the email not the lastname as well. I hope below given query might help.

SELECT EMPLOYEE_ID, FIRST_NAME, Last_Name, EMAIL FROM EMPLOYEES WHERE EMAIL NOT Like '' LEFT(FIRST_NAME,1)   Last_Name  '%'