Making a custom id

Question

I've stumbled into rather an interesting and difficult problem. Assume I've got a table like with 3 columns:

id, which is VARCHAR(6)
a, which is VARCHAR(45)
b, which is VARCHAR(45)

I'm trying to generate a custom id of format AB0001, where A - first letter from a column; B - first letter from b column; the rest - 4 digits (possibly zerofill?) with auto-increment for each letter combination. My current approach would be assigning on insert trigger with a procedure to generate one, although is it possible to make it more simple and how?

Ren · Answer 1 · 2013-06-09T16:56:42.823

0

Have the table like:

   MyTable
   (
        pk int,
        id varchar(6),
        a varchar(45),
        b varchar(45)
   )

put a primary key constraint on pk. Put an index on pk and set the increment on the index to 1..

 Create Procedure spMyTable_Inserrt
 (
   a varchar(45),
   b varchar(45)
 ) 

 As     

 insert into MyTable (a, b)
 values (a, b)

 return;
 GO

Trigger

 CREATE Trigger trgAfterInsert ON MyTable
 AS

 declare @pk as int
 declare @id as int

 select @pk= i.pk from inserted i;

 select @id = case @pk % 2 
                     where 1 then Left(a, 1) + Left(a, 1) + Right('0000' + cast(@pk as nvarchar), 4)
                     where 0 then Left(a, 1) + Left(b, 1) + Right('0000' + cast(@pk - 1 as nvarchar), 4)

 Update MyTable
     set id = @id
 Where pk = @pk 

 GO

Although the syntax can be messed up as I wrote for T-SQL. Also, if you are not so inclined on creating a primary key for whatever reasons, use row count in the trigger to construct @id.

edited Jun 09 '13 at 16:56

answered Jun 09 '13 at 10:26

Ren

437
4
17

forgot to mention the catchy place, that there could be AA0001 and AB0001, which are different although the first ids for such letter combination. – psukys Jun 09 '13 at 10:56
Plz elaborate.. Where do you get to generate AA0001 and where AB0001? Check the edit now. – Ren Jun 09 '13 at 10:59
For example, We have a table without any rows and start inserting some: 1st row a,b get values {A,A}, therefore the id should be AA0001 (let's say it's the initial value) 2nd row a,b get values {A,B}, therefore the id should be AB0001. 3rd row a,b get values {A,A}, therefore the id should be AA0002 – psukys Jun 09 '13 at 11:04
In other words, the variety of ids should be 2 * *9999 – psukys Jun 09 '13 at 11:06

Making a custom id

1 Answers1