Click HERE to see how Saviynt Intelligence is transforming the industry. |
06/20/2024 10:38 PM
Hello team,
I have one email and username and email generation logic requirement.
Condition:
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'
)
06/20/2024 10:55 PM
CONCAT( LOWER(REPLACE( IF( LENGTH(CONCAT_WS('_', users.firstname, users.lastname)) >= 20, IF(LENGTH(users.firstname) > LENGTH(users.lastname), LEFT(users.firstname, 1), users.firstname ), IF( LOCATE(' ', users.firstname) > 0, REPLACE(users.firstname, ' ', ''), IF(LENGTH(users.firstname) >= 20, LEFT(users.firstname, 1), users.firstname ) ) ), ' ', '' )), '_', LOWER(REPLACE( IF( LENGTH(CONCAT_WS('_', users.firstname, users.lastname)) >= 20, IF(LENGTH(users.firstname) > LENGTH(users.lastname), users.lastname, LEFT(users.lastname, 1) ), 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 ) ) ) ), ' ', '' )), IF( EXISTS( SELECT 1 FROM Users u2 WHERE u2.email = CONCAT( LOWER(REPLACE( IF( LENGTH(CONCAT_WS('_', users.firstname, users.lastname)) >= 20, IF(LENGTH(users.firstname) > LENGTH(users.lastname), LEFT(users.firstname, 1), users.firstname ), IF( LOCATE(' ', users.firstname) > 0, REPLACE(users.firstname, ' ', ''), IF(LENGTH(users.firstname) >= 20, LEFT(users.firstname, 1), users.firstname ) ) ), ' ', '' )), '_', LOWER(REPLACE( IF( LENGTH(CONCAT_WS('_', users.firstname, users.lastname)) >= 20, IF(LENGTH(users.firstname) > LENGTH(users.lastname), users.lastname, LEFT(users.lastname, 1) ), 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( '_', IFNULL( (SELECT COUNT(*) FROM Users u3 WHERE u3.email LIKE CONCAT( LOWER(REPLACE( IF( LENGTH(CONCAT_WS('_', users.firstname, users.lastname)) >= 20, IF(LENGTH(users.firstname) > LENGTH(users.lastname), LEFT(users.firstname, 1), users.firstname ), IF( LOCATE(' ', users.firstname) > 0, REPLACE(users.firstname, ' ', ''), IF(LENGTH(users.firstname) >= 20, LEFT(users.firstname, 1), users.firstname ) ) ), ' ', '' )), '_', LOWER(REPLACE( IF( LENGTH(CONCAT_WS('_', users.firstname, users.lastname)) >= 20, IF(LENGTH(users.firstname) > LENGTH(users.lastname), users.lastname, LEFT(users.lastname, 1) ), 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 ) ) ) ), ' ', '' )), '_%' )), 1 ) ), '' ), '@gmail.com' ) |