Hi all -
Let's say the user signs up for an account on my site and they need to
pick a unique user name. They type in:
MikeChristensen
However, me and several of my dopplegangers already have accounts.
Thus, the users table already has:
MikeChristensen1
MikeChristensen2
MikeChristensen3
MikeChristensen4
I want to write a SQL query that figures out that MikeChristensen5 is
the next available username and thus suggest it. Here's some things I
could do:
1) Just tack a random number at the end and check it to make sure it's
unique, if not then loop. I might do a few SQL calls but it would
work. However, it looks a bit hacky if I say "Do you want to create
the account MikeChristensen394783?"
2) I could write a DO...WHILE loop in a SQL function that first checks
if the base name is available, and then tacks on a number and
increments it until the name is available. However, this gets slower
and slower and has to run one query per name. The username "Joe"
might require 1,000 queries someday. Maybe this could be optimized by
incrementing more each loop.
3) Do something really funky with the schema, attempting to store the
next available suffix for each name somewhere in a table. This gets
messy the more I think about it. Ick.
4) Do a SELECT on each row that starts with "MikeChristensen" and then
trying to append the row count to the end, this might not be exact but
it's somewhat intelligent as a starting point. However, this might
require some special indexes on this table to quickly scan rows that
start with a certain name. However, if I get to the point where this
becomes slow then I can worry about it at that point since this would
only be run on new account creation and not super speed critical.
This post isn't really a how-to question, just looking for someone who
wants to toss out a few ideas for avenues to explore.. Thanks!!
Mike
