Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Email generation logic query.

RT
New Contributor II
New Contributor II

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'
)

 
1 REPLY 1

rushikeshvartak
All-Star
All-Star
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'
)

Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.