How to loop the data and if username exists append incremental number e.g. JOHSMI1 or JOHSMI2

I have a userid table

UserId

JHOSMI

KALVIE

etc...

What I would like to do is create开发者_Go百科 a select statement and pass user id, if the userid already exists then append 1 to the id, This gets complicated if you already have JHOSMI, JHOSMI1, then I want to return JHOSMI2.

Really appreciate help here.

Thanks in advance

edited 21-Jul

this is what i got so far.. but not working the way

    select @p AS StaffID,
        @old_p := @p,
    @Cnt := @Cnt+1 As Lvl,
         (SELECT  @p :=Concat(@i, @Cnt)
         FROM   departmenttaff
         WHERE   upper(trim(UserId)) = upper(trim(StaffID))
       AND upper(trim(department)) like  upper(trim('SERVICE'))
         ) AS dummy
 FROM    (
         SELECT  
        @i := upper(trim('JOHSMI')),
        @p := upper(trim('JOHSMI')),
                 @old_p :='',
        @Cnt:=0
        ) vars,
        departmenttaff p
WHERE    @p <> @old_p
order by Lvl Desc LIMIT 1;

---

This will do exactly what you want. You will need a unique constraint on your column. You might also need to add in error code if success = 0. This is in MSSQL, you will need to add the relevant commands for MySQL. I do not have MySQL so I cannot test it.

NOTE: You can replace the try catch with some IF EXISTS logic. I just prefer the try catch because its more stable for multiple threads.

begin tran
select * from #tmp


declare @success bit
declare @name varchar(50)
declare @newname varchar(50)
declare @nextid int
declare @attempts int
set @name = 'brad2something'
set @success = 0
set @attempts = 0

while @success = 0 and @attempts < 5 begin

    begin try

        set @attempts = @attempts + 1 -- failsafe

        set @newname = @name

        if exists (select * from #tmp where username = @name) begin

            select @nextid = isnull(max(convert(int, substring(username, LEN(@name) + 1, 50))), 0) + 1
            from #tmp where username like @name + '%' and isnumeric(substring(username, LEN(@name) + 1, 50)) = 1

            set @newname = @name + CONVERT(varchar(20), @nextid)
        end

        insert into #tmp (username) values (@newname)

        set @success = 1

    end try begin catch end catch

end



--insert into #tmp (username)
--select 
select @success
select * from #tmp
rollback


/*
drop table #tmp
create table #tmp (
username varchar(50) not null unique
)

insert into #tmp (username)
select 'brad'
union all select 'brad1'
union all select 'brad2something5'
union all select 'brad2'
union all select 'laney'
union all select 'laney500'
*/

---

I noticed you want to back fill data. If you want to back fill then this will work. It is extremely inefficient but there is no way around it. There is optimizing code you can put in for when an "error" occurs to prevent all previous counts from happening, but this will work.

begin tran
select * from #tmp


declare @success bit
declare @name varchar(50)
declare @newname varchar(50)
declare @nextid int
declare @attempts int
set @name = 'laney'
set @success = 0
set @attempts = 0
set @nextid = 1

while @success = 0 and @attempts < 5 begin

    begin try

        if exists (select * from #tmp where username = @name) begin
            set @newname = @name + CONVERT(varchar(20), @nextid)

            while exists (select * from #tmp where username = @newname) begin
                set @nextid = @nextid + 1
                set @newname = @name + CONVERT(varchar(20), @nextid)
            end
        end else
            set @newname = @name

        set @attempts = @attempts + 1 -- failsafe

        insert into #tmp (username) values (@newname)

        set @success = 1

    end try begin catch end catch

end



--insert into #tmp (username)
--select 
select @success
select * from #tmp
rollback


/*
drop table #tmp
create table #tmp (
username varchar(50) not null unique
)

insert into #tmp (username)
select 'brad'
union all select 'brad1'
union all select 'brad2something5'
union all select 'brad2'
union all select 'laney'
union all select 'laney500'
*/

---

Is it mandatory to have the count in same column? its better to have it in a different integer column. Anyways, if this is the requirement then select userid from table where userid like 'JHOSMI%', then do extract the number using mysql substr function.

---

For other people who might find this, here's a version in PostgreSQL:

create or replace function uniquify_username(varchar) returns varchar as $$
select $1 || coalesce((max(num) + 1)::varchar, '')
from 
  (select 
     substring(name, '^(.*?)[0-9]*$') as prefix, 
     coalesce(substring(name, '.*([0-9]+)$'), '0')::integer as num 
   from user1) users
where prefix = $1
$$ LANGUAGE sql;

I think it could be adapted to MySQL (though probably not as a stored procedure) but I don't have a MySQL server handy to do the conversion on.

---

Put a UNIQUE constraint on the column.

You didn't say what language you are using, so use this pseudo code

counter = 0
finished = false
while finished = false
{
  try
  {
    if counter >= 1 then name = name + counter
    counter = counter + 1
    insert into table (name)
  }
}

This code is extremely finicky. But will get the job done and there is no real other way to do this except for in sql, and you will always have some type of try catch to avoid two processes running at the same time. This way you use the unique key constraint to force the error, and supress it because it is expected.

I in no way condone using try/catch for business logic like this, but you are putting yourself in a situation thats unavoidable. I would say put the ID in a seperate column and make a unique constraint on both fields.

Proper solution:

Columns: Name, ID, Display Name

Unique constraint on: Name, ID

Display Name is a computed column (virtual) is Name + ID

If you do it this way, then all you have to do is INSERT INTO table (name, (select max() from table))