SQLServer IDENTITY Column with text


Identity column must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable. So it is not possible to add a varchar column as Identity Column.

Alternative ways to add varchar column as Identity column:

Alternate 1:
By using computed column:

Examlple:

CREATE TABLE dbo.MyTable1
(
    Id int NOT NULL PRIMARY KEY,
    CombinedId AS 'ABCD-' + CAST(Id as varchar(16)),
    Name varchar(50)
)

CREATE TABLE dbo.MyTable
(
    Id int NOT NULL PRIMARY KEY,
    PrefixField varchar(16),
    CombinedId AS PrefixField + CAST(Id as varchar(16))
)

Alternate 1:
We can Increment alphanumeric column
Below is the function used to Increment alphanumeric column

CREATE FUNCTION dbo.fn_IncrementAlphaNumericString
(
      @string nvarchar(100)
) 
/*****************************************************************
** Name           :     Sequential Alpha Numeric String Incrementer
**
** Description    :     This function will increment the given alpha
**                numeric string in sequential order up to 100
**                characters. (The length can be easily modified)
**                           
** Parameters     :     nvarchar string (100)
**
** Returns        :     nvarchar string (100)                                                         
**                 
*****************************************************************/
RETURNS nvarchar(100) AS 
BEGIN
DECLARE     @InputStringLen int, @LastPos int, @position int,
            @NewString nvarchar(100), @NumericString nvarchar(100),
            @MaxNumValue nvarchar(100),  @AlphaString nvarchar(100),
            @MaxStringValue nvarchar(100)

SET @position = 1

--Check to see if the given string is numeric.
IF ISNUMERIC(@string) <> 0
BEGIN
--The string is numeric so check to see if is at it's maximum numeric value.
  SET @MaxStringValue = REPLICATE('9',LEN(@string))
  IF @MaxStringValue = @string
  BEGIN
--The string is at it's maximum numeric value so add an alpha character.
      SET @NewString = 'A' + REPLICATE('0',LEN(@string)-1)
  END
  ELSE
  BEGIN
--The number isn't at it's maximum numeric value so increment the number and pad with the zero's if necessary.
      SET @NewString = RIGHT(REPLICATE('0',LEN(@string)) + CAST((CAST(@string as int) + 1) as nvarchar),LEN(@string))
  END
END
ELSE
BEGIN
--The string isn't numeric so find the position of the last alpha character
--by looping through the string character by character.
 WHILE @position <= LEN(@string)
 BEGIN
      IF ISNUMERIC(SUBSTRING(@string,@position,1)) = 0
      BEGIN
            SET @LastPos = @position
      END

      SET @position = @position + 1
 END

--Make sure the last alpha position is less than the length of the whole string.
 IF @LastPos < LEN(@string)
 BEGIN
      --Get the alpha portion of the string and change to uppercase characters so the acii range is correct.
      SET @AlphaString = UPPER(SUBSTRING(@string,1,@LastPos))
      --Get the numeric portion of the string.
      SET @NumericString = SUBSTRING(@string,@LastPos +1,LEN(@string))
      --Prepare a variable with the maximum numeric value to compare against the strings numeric value.
      SET @MaxNumValue = REPLICATE('9',LEN(SUBSTRING(@string,@LastPos+1,LEN(@string))))
      --Compare the numeric value of the string against the maximum numeric value .
      IF  @MaxNumValue = @NumericString
      BEGIN
            --The numeric value of the string has reached the maximum value so check to see if the position
            --of the last alpha character has reached the end of the string.
            IF @LastPos < (LEN(@string) -1)
            BEGIN
            --The alpha characters haven't reached the end of the string so add another alpha character.
                  SET @NewString = @AlphaString + 'A' + REPLICATE('0',LEN(@NumericString)-1)
            END
            ELSE
            BEGIN
            --The alpha characters have reached the end of the string so check to see if the last alpha
            --character has reached it's maximum ascii value.
                  IF ASCII(SUBSTRING(@string,@LastPos,1)) = 90
                  BEGIN
                  --The last alpha character has reached it's maximum ascii value so find the position of the
                  --first alpha character that has reached it's maximum ascii value.
                        DECLARE @pos int
                        SET @pos = @LastPos
                        WHILE ASCII(SUBSTRING(@AlphaString,@pos,1)) = 90
                        BEGIN
                              SET @pos = @pos - 1
                        END
                        --Check to see if the position of the first alpha character that has reached it's maximum
                        --ascii value is the first character in the whole string.
                        IF @pos > 1
                        BEGIN
                        --The first alpha character that has reached it's maximum ascii value isn't the first in the
                        --string so add the first alpha characters to the beginning of the new string and increment
                        --those that have reached their maximum value.
                              SET @NewString = LEFT(SUBSTRING(@AlphaString,1,@pos -1) + CHAR(ASCII(SUBSTRING(@AlphaString,@pos,1))+1) + REPLICATE('0',LEN(@string)),LEN(@string))
                        END
                        ELSE
                        BEGIN
                        --The first alpha character that has reached it's maximum ascii value is the first character in
                        --the string so increment it and pad the remainder of the string with zero's.
                              SET @NewString = LEFT(CHAR(ASCII(SUBSTRING(@string,1,1))+1) + REPLICATE('0',LEN(@string)),LEN(@string))
                        END
                  END
                  ELSE
                  BEGIN
                        --The last alpha character hasn't reached it's maximum ascii value so check to see if it is the first
                        --character in string.
                        IF @LastPos > 1
                        BEGIN
                        --The last alpha character isn't the first in the string so add the first alpha characters to the
                        --beginning of the new string and increment it and pad with zero's.
                              SET @NewString = LEFT(SUBSTRING(@string,1,@LastPos -1) + CHAR(ASCII(SUBSTRING(@string,@LastPos,1))+1) + REPLICATE('0',LEN(@string)),LEN(@string))
                        END
                        ELSE
                        BEGIN
                        --The last alpha character is the first in the string so increment it and pad the remainder of the string with zero's.
                              SET @NewString = LEFT(CHAR(ASCII(SUBSTRING(@string,1,1))+1) + REPLICATE('0',LEN(@string)),LEN(@string))
                        END
                  END
            END
      END
      ELSE
      BEGIN
      --The numeric value of the string hasn't reached the maximum value so only increment the numeric portion of the string and pad with zero's if necessary.
            SET @NewString = SUBSTRING(@string,1,@LastPos) + RIGHT(REPLICATE('0',LEN(@NumericString)) + CAST((CAST(SUBSTRING(@string,@LastPos +1,LEN(@string)) as int)+1) as nvarchar), LEN(@NumericString))
      END
  END
END

RETURN @NewString
END

Usage:

select dbo.fn_IncrementAlphaNumericString('abcd123')


No comments:

Post a Comment