Best way to generate fixed length reference number using identity

Question

Best way to generate fixed length reference number using identity

Daniel Taylor-446457

Mr or Mrs. 500

Points: 502
More actions
October 19, 2018 at 3:23 am

#379906

Hi,
I have a table with column [CustomerID] [int] IDENTITY(1,1).
I would like to create a fixed width 'user friendly' refernec number e.g. CN000001, CN000002 etc...
It can be done in a VIEW ... but id like in the main table.
This got me wondering what the best way to do this might be.
The trigger below works ... but is there a trick im missing with Identity, computed columns etc... or a more efficent trigger?
Any advice welcomed
Thanks
/*
Triggers uses column ([CustomerID] [int] IDENTITY(1,1) )
and creates a user friendly Customer Number e.g. CN000123
*/
CREATE TRIGGER [tr_tDeals_cnCustID] ON [FedCapDB_SQL_TEST].[dbo].[tCustomer]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [tCustomer]
    SET cnCustomerID = 'CN' + RIGHT('00000' + CAST(t.[CustomerID] AS VARCHAR(8)), 5)
    FROM [tCustomer] t
    WHERE t.cnCustomerID IS NULL
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Why not use a PERSISTED Computed Column? In a very simple sense:

CREATE TABLE SomeTable (ID int IDENTITY(1,1),
         CustomerID AS 'CN' + RIGHT('000000' + CONVERT(varchar(6),ID),6) PERSISTED,
          Firstname varchar(50),
         Surname varchar(50));
INSERT INTO SomeTable (FirstName, Surname)
VALUES ('Steve','Jobs'),
   ('Jane','Smith'),
    ('Sally','Jones');
SELECT *
FROM SomeTable;

This would probably be far easier than a trigger.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Daniel Taylor-446457 Mr or Mrs. 500 Points: 502 More actions · Answer 2

This is perfect - seem slicker than a Trigger.
This is just the kind of reply i was looking for.
Many Thanks
Dan