Re: Query to get the "next available" unique suffix for a name

On Mon, Sep 27, 2010 at 06:36:25PM -0700, Mike Christensen wrote:
> 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.

Why not do something like:

SELECT max(nullif(substring(username FROM '[0-9]*$'),'')::numeric) AS lastnum
FROM users
WHERE username ~ '^MikeChristensen[0-9]*$';

It's a pretty direct translation from what I'd do in any imperative
language.

--
Sam http://samason.me.uk/