set nocount on
use northwind

if object_id('dbo.fnCustomCode') > 0
	drop function dbo.fnCustomCode
go

create function dbo.fnCustomCode(@i int)
returns char(5)
as
begin
	--translates in between 0 and 675999
	--to  AA000 to ZZ999.

	--check for valid range
	if @i < 0 or @i > 675999
	begin
		return cast('RANGE' as char(5))
	end
	declare	@pk char(5)
		,@letter char(26)
	set @letter = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
	set @pk =
		substring(@letter, ((@i/1000)/26)+1, 1)
		+ substring(@letter, ((@i/1000)%26)+1, 1)
		+ right('000' + convert(varchar,@i % 1000),3)
	
	return @pk
end

go
create table Junk 
	(junkid int identity(0,1) primary key not null
	,CustomCode as dbo.fnCustomCode(junkid))
go

set identity_insert junk on
insert junk (junkid)
select -1 union
select	0 union 
select	1 union
select 999 union 
select 1000 union
select 2000 union
select 26999 union
select 27000 union
select 675999 union
select 676000
set identity_insert junk off

select * from junk

go

drop table junk
drop function dbo.fnCustomCode

declare	@PartID int
set	@PartID = 675999
select	char(ascii('A')+(@PartID / 26000)) +  char(ascii('A')+(@PartID / 1000) % 26) + right('000' + cast((@PartID % 1000) as varchar(3)), 3)

quote:

---

Originally posted by blindman

Go ahead and use a standard incrementing identiy as your primary key, and then let modulo arithmetic come to your rescue:

declare	@PartID int
set	@PartID = 675999
select	char(ascii('A')+(@PartID / 26000)) +  char(ascii('A')+(@PartID / 1000) % 26) + right('000' + cast((@PartID % 1000) as varchar(3)), 3)

What you will do after 675,999 parts is up to your client.

---

create table MyTable
(
PK		int not null
identity(1,1) 	primary key clustered,
PART_DESC	varchar(20)	not null,
PART_CODE as
	convert(char(5), 
	char(ascii('A')+(PK / 26000)) +
	char(ascii('A')+(PK / 1000) % 26) +
	right('000' + cast((PK % 1000) as varchar(3)), 3))

)

insert into MyTable (PART_DESC) select 'My Part Desc'

select * from MyTable

drop table MyTable


(1 row(s) affected)

PK          PART_DESC            PART_CODE 
----------- -------------------- --------- 
1           My Part Desc         AA001

(1 row(s) affected)