System Generation Username

[cdavis2]

Hello,

        I am trying to modify my generation username to use first initial lastname plus a number. I also want it to use full first name and full last name if the last name is less then two characters. All of this works with my query but I ran into a different scenario where the last name is two letters and a space between the letters. I thought the Replace function that I have set would solve for this but it appears to be ignoring that. Any assistance will be appreciated.

case when length(trim(users.lastname)) between 2 and 500 then
concat
(
substring(users.firstname,1,1),replace(replace(replace(replace(users.lastname, ' ',''),' ', ''), '-', ''), '_', '')
,
case
when (select count(*) from users X where substring(X.firstname,1,1) = substring(users.firstname,1,1) and X.lastname = users.lastname) in (0 , 1) then ''
else
(select count(*) from users X where substring(X.firstname,1,1) = substring(users.firstname,1,1) and X.lastname = users.lastname)
end
)
else
concat(users.firstname, users.lastname,
case
when (select count(*) from users X where X.firstname = users.firstname and X.lastname = users.lastname) in (0 , 1) then ''
else
(select count(*) from users X where X.firstname = users.firstname and X.lastname = users.lastname)
end
)
end 

[rushikeshvartak]

CASE WHEN LENGTH(TRIM(users.lastname)) BETWEEN 2 AND 500 THEN CONCAT(SUBSTRING(users.firstname, 1, 1), REPLACE(REPLACE(REPLACE(REPLACE(users.lastname, ' ', ''), '-', ''), '_', ''), ' ', ''), CASE WHEN (SELECT COUNT(*) FROM users X WHERE SUBSTRING(X.firstname, 1, 1) = SUBSTRING(users.firstname, 1, 1) AND X.lastname = users.lastname) IN (0, 1) THEN '' ELSE CAST((SELECT COUNT(*) FROM users X WHERE SUBSTRING(X.firstname, 1, 1) = SUBSTRING(users.firstname, 1, 1) AND X.lastname = users.lastname) AS CHAR) END) ELSE CONCAT(users.firstname, users.lastname, CASE WHEN (SELECT COUNT(*) FROM users X WHERE X.firstname = users.firstname AND X.lastname = users.lastname) IN (0, 1) THEN '' ELSE CAST((SELECT COUNT(*) FROM users X WHERE X.firstname = users.firstname AND X.lastname = users.lastname) AS CHAR) END) END

[cdavis2]

I tested and got the following result

[rushikeshvartak]

CASE WHEN LENGTH(TRIM(replace(users.lastname,' ',''))) BETWEEN 3 AND 500 THEN CONCAT(SUBSTRING(users.firstname, 1, 1), REPLACE(REPLACE(REPLACE(REPLACE(users.lastname, ' ', ''), '-', ''), '_', ''), ' ', ''), CASE WHEN (SELECT COUNT(*) FROM users X WHERE SUBSTRING(X.firstname, 1, 1) = SUBSTRING(users.firstname, 1, 1) AND replace(X.lastname,' ','') = replace(users.lastname,' ','')) IN (0, 1) THEN '' ELSE CAST((SELECT COUNT(*) FROM users X WHERE SUBSTRING(X.firstname, 1, 1) = SUBSTRING(users.firstname, 1, 1) AND replace(X.lastname,' ','') = replace(users.lastname,' ','')) AS CHAR) END) ELSE CONCAT(users.firstname, replace(users.lastname,' ',''), CASE WHEN (SELECT COUNT(*) FROM users X WHERE X.firstname = users.firstname AND replace(X.lastname,' ','') = replace(users.lastname,' ','')) IN (0, 1) THEN '' ELSE CAST((SELECT COUNT(*) FROM users X WHERE X.firstname = users.firstname AND replace(X.lastname,' ','') = replace(users.lastname,' ','')) AS CHAR) END) END