locked
Sql Autoincrement by alphanumeric RRS feed

  • Question

  • User-1257538633 posted

    What is the query for autoincrement the alphanumeric terms?

    for example: nss1,nss2..

    Friday, November 22, 2013 1:13 AM

All replies

  • User2103319870 posted

    Hi,

    You can acheive the functionality using Ranking Functions

    Try using the below code

    select
    'nss'+RIGHT(CAST((ROW_NUMBER() OVER (ORDER BY YourColumnName)) AS VARCHAR(8)), 4)
    	AS [ID]
    ,
    YourCOlumnName
    FROM YourTableName WITH(NOLOCK)

    Change your columnname and tablename as per your design

    Friday, November 22, 2013 1:27 AM
  • User-510141866 posted
    DECLARE @COUNT INT
    DECLARE @NEWID VARCHAR(50)
    SET @COUNT=(SELECT ISNULL(MAX(FIELD),0)+1 FROM TABLE_NAME)
    SET @NEWID='NSS'+CONVERT(VARCHAR(50),@COUNT)

    Wednesday, December 4, 2013 7:28 AM
  • User364663285 posted
    1. Use substring to get numeric part
    2. increment the number
    3. get concatenation of string part and new numeric part
    Monday, January 20, 2014 2:53 AM