Hello team,
I have one email and username and email generation logic requirement.
Condition:
1)If the user has one word in firstname and lastname
Logic firstname_lastname@gmail.com
2)If the user has more than one word in firstname
Logic: all words of firstname_lastname@gmail.com
3)If the user has more than one word in lastname
Logic : firstname_Last word of lastname@gmail.com
4)If the user already exist with same email
Logic: firstname_lastname_number@gmail.com
firstname_lastname_2@gmail.com
firstname_lastname_3@gmail.com
firstname_lastname_4@gmail.com, etc
5)If the user has very long firstname (character in firstname greater and equals to 20)
Logic :first character of firstname_lastname@gmail.com
6)If the user has very long lastname (character in lastname greater and equals to 20)
Logic :firstname_first character of lastname@gmail.com
7)If the user does not have lastname
Logic : firstname_number@gmail.com
firstname_1@gmail.com
firstname_2@gmail.com etc
8)Combine condition including above conditions:
If while creating email firstname_lastname total character is equals or greater than 20 then
Logic :first character of firstname_lastname@gmail.com (firstname long than lastname)
Logic :firstname_first character of lastname@gmail.com (lastname long than firstname)
I prepared query to achieve this condition where my 8th condition fails.
Please check the below query and help me to update for this last condition without disturbing other conditions.
CONCAT(
LOWER(REPLACE(
IF(
LOCATE(' ', users.firstname) > 0,
REPLACE(users.firstname, ' ', ''),
IF(LENGTH(users.firstname) > 20,
LEFT(users.firstname, 1),
users.firstname
)
),
' ', ''
)),
'_',
LOWER(REPLACE(
IF(
users.lastname IS NULL OR users.lastname = '',
'1',
IF(LENGTH(users.lastname) > 20,
LEFT(SUBSTRING_INDEX(users.lastname, ' ', -1), 1),
IF(LOCATE(' ', users.lastname) > 0,
SUBSTRING_INDEX(users.lastname, ' ', -1),
users.lastname
)
)
),
' ', ''
)),
'@gmail.com'
) #CONCAT(
LOWER(REPLACE(
IF(
LOCATE(' ', users.firstname) > 0,
REPLACE(users.firstname, ' ', ''),
IF(LENGTH(users.firstname) > 20,
LEFT(users.firstname, 1),
users.firstname
)
),
' ', ''
)),
'_',
LOWER(REPLACE(
IF(
users.lastname IS NULL OR users.lastname = '',
'2',
IF(LENGTH(users.lastname) > 20,
LEFT(SUBSTRING_INDEX(users.lastname, ' ', -1), 1),
IF(LOCATE(' ', users.lastname) > 0,
SUBSTRING_INDEX(users.lastname, ' ', -1),
users.lastname
)
)
),
' ', ''
)),
'_2',
'@gmail.com'
) #CONCAT(
LOWER(REPLACE(
IF(
LOCATE(' ', users.firstname) > 0,
REPLACE(users.firstname, ' ', ''),
IF(LENGTH(users.firstname) > 20,
LEFT(users.firstname, 1),
users.firstname
)
),
' ', ''
)),
'_',
LOWER(REPLACE(
IF(
users.lastname IS NULL OR users.lastname = '',
'3',
IF(LENGTH(users.lastname) > 20,
LEFT(SUBSTRING_INDEX(users.lastname, ' ', -1), 1),
IF(LOCATE(' ', users.lastname) > 0,
SUBSTRING_INDEX(users.lastname, ' ', -1),
users.lastname
)
)
),
' ', ''
)),
'_3',
'@gmail.com'
) #CONCAT(
LOWER(REPLACE(
IF(
LOCATE(' ', users.firstname) > 0,
REPLACE(users.firstname, ' ', ''),
IF(LENGTH(users.firstname) > 20,
LEFT(users.firstname, 1),
users.firstname
)
),
' ', ''
)),
'_',
LOWER(REPLACE(
IF(
users.lastname IS NULL OR users.lastname = '',
'4',
IF(LENGTH(users.lastname) > 20,
LEFT(SUBSTRING_INDEX(users.lastname, ' ', -1), 1),
IF(LOCATE(' ', users.lastname) > 0,
SUBSTRING_INDEX(users.lastname, ' ', -1),
users.lastname
)
)
),
' ', ''
)),
'_4',
'@gmail.com'
) #CONCAT(
LOWER(REPLACE(
IF(
LOCATE(' ', users.firstname) > 0,
REPLACE(users.firstname, ' ', ''),
IF(LENGTH(users.firstname) > 20,
LEFT(users.firstname, 1),
users.firstname
)
),
' ', ''
)),
'_',
LOWER(REPLACE(
IF(
users.lastname IS NULL OR users.lastname = '',
'5',
IF(LENGTH(users.lastname) > 20,
LEFT(SUBSTRING_INDEX(users.lastname, ' ', -1), 1),
IF(LOCATE(' ', users.lastname) > 0,
SUBSTRING_INDEX(users.lastname, ' ', -1),
users.lastname
)
)
),
' ', ''
)),
'_5',
'@gmail.com'
)
