+13 votes
by (13.6m points)

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.

2 Answers

+1 vote
by (13.6m points)

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

0 votes
by (13.6m points)

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