How do I get first name, middle name and last name as username with a dot in between the names and a number at the end for a unique username?

Question

I'm working on a project, but I would like to know how I get the first name, middle name and last name as the username with a dot in between their full names and a number at the end for a unique username? In MySQL. Just like Facebook's system. I also want so if the middle name is NULL , then it doesn't like a space in the middle.

I have tried a lot of different things, but nothing worked yet.

SELECT COALESCE(CONCAT(first_name, '.', COALESCE(CONCAT(middle_name, '.', last_name), 
first_name,middle_name,last_name)), middle_name,'.', COALESCE(CONCAT(first_name, '.', 
last_name),first_name,last_name)) AS username FROM users

That is how close I have come, but it has a problem if they don't have a middle name, it will display two dots instead of one. But I still need something to get the first name, middle name and last name as the username with a dot in between their full names and a number at the end for a unique username.

Answer 1

I don't know what you want for the number at the end, but for the name, use concat_ws() :

select concat_ws('.', first_name, middle_name, last_name)

If any of the values are NULL then the value is ignored (so f.l rather than f..l ).

If you still want separators when the first/last name are NULL , then use coalesce() :

select concat_ws('.', coalesce(first_name, ''), middle_name, coalesce(last_name, ''))

You can concat a number at the end using concat() or by adding it as an argument (in which case, there will be a dot before the number).

Answer 2

If you want numbers at the end of the username then you can use the id or SL value of the user. The ID or SL column should be NOT NULL , AUTO INCREMENT . You can use:

select concat_ws('.', first_name, middle_name, last_name, id)