Search content within the blog

Monday, May 18, 2009

AutoGenerate an AlphaNumeric Sequence in SQL Server

Unique Alphanumeric Sequence Number to be generated using the following business rules:
UniqueID = Code (TT) + Current Datetime + ID

DECLARE @TT TABLE

(

ID int, CircuitName varchar(10),

UniqueID AS 'TT' + REPLACE(CONVERT(varchar, GETDATE(),101),'/','')

+ REPLACE(CONVERT(varchar, GETDATE(),108),':','')

+ CAST(ID as varchar(10))

)



INSERT @TT

SELECT 1, 'Circuit 1' UNION ALL

SELECT 2, 'Circuit 2' UNION ALL

SELECT 3, 'Circuit 3' UNION ALL

SELECT 4, 'Circuit 4' UNION ALL

SELECT 5, 'Circuit 5' UNION ALL

SELECT 6, 'Circuit 6' UNION ALL

SELECT 7, 'Circuit 7' UNION ALL

SELECT 8, 'Circuit 8' UNION ALL

SELECT 9, 'Circuit 9' UNION ALL

SELECT 10, 'Circuit 10'



SELECT * FROM @TT



OUTPUT


ID CircuitName UniqueID

1 Circuit 1 TT051220091517481

2 Circuit 2 TT051220091517482

3 Circuit 3 TT051220091517483

4 Circuit 4 TT051220091517484

5 Circuit 5 TT051220091517485

6 Circuit 6 TT051220091517486

7 Circuit 7 TT051220091517487

8 Circuit 8 TT051220091517488

9 Circuit 9 TT051220091517489

10 Circuit 10 TT0512200915174810

No comments:

Post a Comment