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')
Comments
Post a Comment