• well you are assuming based on your formatting that you'd never have more than 10 people with the same name(xxx09);

    i'm providing an example below, but i don't think like the idea;

    id rather let people use their emails as a username, which at least ends up unique to the end user; i'd hate to comeback to some web site and not be able to remember my "username" was "Lowell312" or something(I'm talking to you, Progressive Insurance!), which was something i didn't pick or even want.

    don't get caught up on the CTE's, they are just generating random data;

    the trick is to use row number and a case statement to generate the string

    --this is just generating fake sample data.

    With MYFirstNames(FName) AS

    (

    Select 'Leonardo' UNION ALL

    Select 'Brad' UNION ALL

    Select 'Arnold' UNION ALL

    Select 'Mark' UNION ALL

    Select 'Matt' UNION ALL

    Select 'Bruce'

    ),

    MyLastNames(LName) AS

    (

    Select 'DeCaprio' UNION ALL

    Select 'Pitt' UNION ALL

    Select 'Schwarzenegger' UNION ALL

    Select 'Wahlberg' UNION ALL

    Select 'Damon' UNION ALL

    Select 'Willis'

    ), NamesWithRowNumber

    AS

    (

    SELECT TOP 10000

    row_number() over (partition by LEFT(A.FName,1),B.LName ORDER BY A.FName,B.LName) - 1 AS RW, --zero indexed

    A.FName,B.LName

    FROM MYFirstNames A

    CROSS JOIN MyLastNames B

    CROSS JOIN sys.columns

    ORDER BY NEWID()

    )

    SELECT

    CASE

    WHEN RW = 0

    THEN LEFT(FName,1)+ LName

    ELSE LEFT(FName,1) + LName + RIGHT('0000' + CONVERT(varchar,rw),4)

    END AS UserName,

    *

    FROM NamesWithRowNumber

    ORDER BY RW,UserName

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!