Auto increment alphanumeric ID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Auto increment alphanumeric ID

Author

Topic

inyathy
Starting Member

2 Posts

Posted - 2014-11-27 : 06:47:57

Hi, I'm using SQL 2008 with table [AgentDetails] and fields [IDCode],[FirstName],[LastName],..etc. [IDCode] is alphanumeric [AAA001].
IS it possible to increment both alpha & numeric when new record is inserted. eg. AAA001,AAA002......AAA999,AAB000,AAB001,...AAB999,AAC000,AAC001...etc. with a user function or some stored procedure.

Hope I'm on the correct form and thanks in advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 09:29:11

You can use a sequence object for this, however due to some restrictions, you cannot make this a computed column. First, create a sequence object:


create sequence foo as int
minvalue 1;
go

Next, in a procedure used to insert new rows, compute the value of IDCode as follows:


declare @foo sql_variant
exec sp_sequence_get_range 'foo', @range_size=1, @range_first_value = @foo out;
declare @int int = cast (@foo as int);
declare @A0 char(1) = char(ascii('A') + (@int/(26*26*1000))%26)
declare @A1 char(1) = char(ascii('A') + (@int/(26*1000))%26)
declare @A2 char(1) = char(ascii('A') + (@int/1000)%26)
declare @IDCode char(6) = @A0 + @A1 + @A2 + right('000' +cast(@int%1000 as varchar(3)), 3)
select @IDCode

Note that you will need to figure out what to do when the sequence object generates a value that would result in IDcode exceeding 6 characters

The restrictions about scalar functions mean that you cannot use sp_sequence_get_range in a function. Hence it will need to be executed as part of the code that inserts new rows.

inyathy
Starting Member

2 Posts

Posted - 2014-11-28 : 07:40:57

Thanks for responce gbritton, I'm using SQL 2008 which does not support a Sequence object but managed to reslove the problem with VB.net Function and returning result to INSERT string.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources