Username is getting generated in numbers using SQL query

[Kaustubh]

Hi,

We are using a query to generate email and username. (attached the file Query_version1.txt).

Later we added a condition to check if customproperty30 is null. Using the query, we are able to generate email successfully, but username is getting generated as numbers. (attached file Query_version2.txt)

[NM]

@Kaustubh what result do you get when you run in data analyzer

[Kaustubh]

I get expected output

[NM]

@Kaustubh but from the username generation rule you are not getting the expected output?

[Kaustubh]

Hi @NM if I put same code (Query_version2.txt) in both username and email generation logic, email is getting generated correctly but username is generated in numbers as image below

If the code is same in email generation and add register user rule, then I expect both email and username to be same which is not happening.

[NM]

@Kaustubh can you share logs at the time of new user creation?

[Kaustubh]

The userkey and username is same

[rushikeshvartak]

IF(users.customproperty30 IS NOT NULL,users.customproperty30,IF(users.lastname is null,
	LOWER(
        CONCAT(
                    REGEXP_REPLACE(users.firstname, '[()<>^ ]', ''), 
                    '_1@domain.com')), 	
	IF(
        LENGTH(
            LOWER(
                CONCAT(
                    REGEXP_REPLACE(users.firstname, '[()<>^ ]', ''), 
                    '_', 
                    REGEXP_REPLACE(SUBSTRING_INDEX(users.lastname, ' ', -1), '[()<>^ ]', '')
                )
            )
        ) < 21,
        LOWER(
            CONCAT(
                REGEXP_REPLACE(users.firstname, '[()<>^ ]', ''), 
                '_', 
				REGEXP_REPLACE(SUBSTRING_INDEX(users.lastname, ' ', -1), '[()<>^ ]', ''),
                '@domain.com'
            )
        ),
        IF(
            LENGTH(REGEXP_REPLACE(users.firstname, '[()<>^ ]', '')) > LENGTH(REGEXP_REPLACE(SUBSTRING_INDEX(users.lastname, ' ', -1), '[()<>^ ]', '')),
            LOWER(
                CONCAT(
                    SUBSTRING(REGEXP_REPLACE(users.firstname, '[()<>^ ]', ''), 1, 1), 
                    '_', 
                   REGEXP_REPLACE(SUBSTRING_INDEX(users.lastname, ' ', -1), '[()<>^ ]', ''),
                    '@domain.com'
                )
            ),
            LOWER(
                CONCAT(
                      REGEXP_REPLACE(users.firstname, '[()<>^ ]', ''),
                    '_', 
					SUBSTRING(REGEXP_REPLACE(users.lastname, '[()<>^ ]', ''), 1, 1), 					
                    '@domain.com'
                )
            )
        )
    )) )