How to increment alphanumeric number in SQL Server

    declare @checkint nvarchar(20)=null
declare @exceptinteger int=null
    DECLARE @CodeToConvert as VARCHAR(20)
    declare @lastintpart nvarchar(20)=null
declare @check nvarchar(20) = null
declare @diff int =null
declare @finalnumber nvarchar(10)=null
declare @minuschecker nvarchar(5) = null


SET @CodeToConvert = 'ABC-123-12'
---- checking is first digit is int
set @checkint =
(
select Left(@CodeToConvert,1)
)

----- find whether number is numberic
set @exceptinteger =
(
   ISNUMERIC(@CodeToConvert)
)

-- if series is alhanumeric
     if(@exceptinteger=0)
begin
           declare @i int=1
               while(@i <= 100)
               begin
       set @check=
   (
   SELECT RIGHT(@CodeToConvert, @i)  
   )
 
        if(ISNUMERIC(@check)=1)
           begin
        set @minuschecker = (select case when cast( @check as int) < 0 then 'Yes' else 'No ' end)
if(@minuschecker = 'No')
begin
                set @lastintpart = Cast(@check AS nvarchar(20))
set @i = @i +1

end
else
begin
break
end
   end
else
   begin
                             break   
   end
    end

     end
-- ELse part if number is not alphnumberic
else
begin
set @lastintpart = @CodeToConvert
end
-- here finally last int part converted into int and incremented by 1
if((select LEN(@lastintpart)) != (select LEN(@CodeToConvert)) )
begin
declare @zeroincludedlen int
declare @afterincrementlen int
set @zeroincludedlen = (select LEN(@lastintpart))


set @lastintpart = cast(@lastintpart as int)+1
set @afterincrementlen = (select LEN(@lastintpart))

if(@zeroincludedlen > @afterincrementlen)
begin    
         set @diff = @zeroincludedlen - @afterincrementlen
set @finalnumber = (select REPLICATE(0, @diff))
  set @lastintpart=  CAST(@finalnumber as nvarchar(10))+ CAST(@lastintpart as nvarchar(20))
 
end
end
    -- here controls comes if number is only ineger  ->
else
begin
 
  ----
  set @zeroincludedlen = (select len(@lastintpart))
 
  ----
      set @lastintpart= CAST(@lastintpart as int)+1
  set @afterincrementlen = (select len(@lastintpart))
  if(@zeroincludedlen > @afterincrementlen)
  begin
  set @diff = @zeroincludedlen - @afterincrementlen
  set @finalnumber = (select REPLICATE(0,@diff))
  set @lastintpart = cast(@finalnumber as nvarchar(10))+ cast(@lastintpart as nvarchar(10))
  end
 
end
-----result is this if only integer---------------------------------------------------
select @lastintpart as onlyinteger

-----
----------This iF Condition is Used to combine first part of alphanumeric number and our incremented integer number ---------
if(@i > 0)
begin
declare @length int
set @length=
(
select len(@CodeToConvert)
)
set @i=@i-1
set  @length = @length-@i
declare @firstpart nvarchar(50)
set @firstpart =
(
select Left(@CodeToConvert,@length)
)
set @firstpart = @firstpart + CAST(@lastintpart as nvarchar(20))
--- result is this if alphanumberic
select @firstpart as finalresult
end

Post a Comment

2 Comments