How to generate alternative user name suggestions?

Apologies in advance for the question title, I can't think of a better way to put it.

In some email systems (say Yahoo), if a name you want is taken, the system will suggest a name by adding a number at the end (for example, if CloudStrife is taken, you get CloudStrife01).

In implement such a system, how do you detect how many duplicates of a name there is (So if there is CloudStrife, CloudStrife01 and CloudStrife02, there should be 3 instances of CloudStrife). Besides storing the number of duplicates there already is, what other solutions are there?

The number of duplicates wouldn't help much anyway, imagine that there will be CloudStrife, you suggest the user CloudStrife01 but he chooses CloudStrife02 instead. So you have two CloudStrifes but still the CloudStrife02 is not available. You should always check against the database. Probably if the name is taken select names which are identical with any suffix and then choose and suggest one which is not in that set.

I would select all usernames using pattern matching.

SELECT username FROM users WHERE username LIKE 'CloudStrife%'

Now we get all usernames that begins with CloudStrife and thus we do not have to do another database query simply to check if the name we're going to recommend is taken or not.

Create 2 or 3 criteria like append birth date at end,add surname at last,change sequence in name and before suggestion if it is already exist or not

If username isn't available:

Strip any numerals from the end of the chosen username. Initialize an integer at 1, then concatenate this integer onto the end of the username stub.

If this username is also taken, increment the integer and repeat the concatenation/check process.