x
Subscribe
emailSubscribe to our mailing list to get the updates to your email inbox...

SQLServer IDENTITY Column with text

By
Advertisement

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')


0 comments:

Post a Comment