locked
AutoIncrement RRS feed

  • Question

  • User-985262370 posted

    hello Everybody

    How to create a string auto Increment like this

    For Example.

    NTAA001

    NTAA002

    ....

    ....

    NTAA999

    NTAB001


    I really appreciate all the help you can give me!
    Thanks in advance

    Sunday, December 12, 2010 9:30 PM

Answers

  • User1469376062 posted

    Hi,


    You can do this easily by making a function or procedure in your database just like

    Here SET @FullName last value of your coloumn. suppose it is NTA-S010.

    you can put below code in a procedure or function for your use.

    DECLARE @FullName VarChar(25)
     SET @FullName = 'NTA-S010'
    declare @lastChar char(1)
    declare @lastValue char(4)
    set @lastChar=SUBSTRING(@FullName,5, 1)
    set @lastValue=SUBSTRING(@FullName,  6 , 8)
    if 99<=convert(int,@lastValue) and convert(int,@lastValue)<=998
      begin
       SET @FullName='NTA-'+@lastChar+convert(char,convert(int,@lastValue)+1)
      end
    if 9<=convert(int,@lastValue) and convert(int,@lastValue)<=98
      begin
       SET @FullName='NTA-'+@lastChar+'0'+convert(char,convert(int,@lastValue)+1)
      end
    if 1<=convert(int,@lastValue) and convert(int,@lastValue)<=8
      begin
       SET @FullName='NTA-'+@lastChar+'00'+convert(char,convert(int,@lastValue)+1)
       end
    if convert(int,@lastValue)=999
      begin
       declare @counter nvarchar(10)
       SET @counter=Ascii(@lastChar)
       SET @counter=@counter+1
        SET @FullName='NTA-'+CHAR(@counter)+'001'
      end
    select @FullName



    I hope it will work for you. If yes then please mark as answer.......


    Thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 13, 2010 4:28 PM

All replies

  • User1224194097 posted

    Where do you want to increment value? In Database. If so, you can create a function to append NTA to the max value from the column.

    Sunday, December 12, 2010 10:40 PM
  • User-985262370 posted

    I want to increase the number first like this

    NTA-A001

    001: records in database

    ...

    999 :records in database

    and then

    it increase

    NTA-B001

    I am using vb.net to write it and save it with XML


    Thanks all

    Sunday, December 12, 2010 10:48 PM
  • User1469376062 posted

    Hi,


    You can do this easily by making a function or procedure in your database just like

    Here SET @FullName last value of your coloumn. suppose it is NTA-S010.

    you can put below code in a procedure or function for your use.

    DECLARE @FullName VarChar(25)
     SET @FullName = 'NTA-S010'
    declare @lastChar char(1)
    declare @lastValue char(4)
    set @lastChar=SUBSTRING(@FullName,5, 1)
    set @lastValue=SUBSTRING(@FullName,  6 , 8)
    if 99<=convert(int,@lastValue) and convert(int,@lastValue)<=998
      begin
       SET @FullName='NTA-'+@lastChar+convert(char,convert(int,@lastValue)+1)
      end
    if 9<=convert(int,@lastValue) and convert(int,@lastValue)<=98
      begin
       SET @FullName='NTA-'+@lastChar+'0'+convert(char,convert(int,@lastValue)+1)
      end
    if 1<=convert(int,@lastValue) and convert(int,@lastValue)<=8
      begin
       SET @FullName='NTA-'+@lastChar+'00'+convert(char,convert(int,@lastValue)+1)
       end
    if convert(int,@lastValue)=999
      begin
       declare @counter nvarchar(10)
       SET @counter=Ascii(@lastChar)
       SET @counter=@counter+1
        SET @FullName='NTA-'+CHAR(@counter)+'001'
      end
    select @FullName



    I hope it will work for you. If yes then please mark as answer.......


    Thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 13, 2010 4:28 PM
  • User-985262370 posted

    It is useful for me,I will try it out.

    Thanks all

    Phuc Hoang

    Monday, December 13, 2010 8:11 PM