CODEWITHSUNDEEP
sql-serveruniqueidentifier
mpora
mpora

Reputation: 1479

Generating a unique id in a specified format

I am working on a system that has to generate a unique reference number in the following format:

[A-Z]{2}[current][8 randomized alpha/numerical characters]

Many users could be using the system at the same time.

So my question is how to ensure that this reference number is unique all the time. After a year has elapsed, the 8 randomized characters can be reused. This number will be stored in the database.

Upvotes: 0

Views: 273

Answers (2)

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

  declare @abc varchar(36) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'

  select char(cast(rand()*1000  as int)% 25+65) 
  + char(cast(rand()*1000  as int)% 25+65) + '[current]'
  + SUBSTRING(@abc,cast(rand()*1000  as int)% 36+1,1)
  + SUBSTRING(@abc,cast(rand()*1000  as int)% 36+1,1)
  + SUBSTRING(@abc,cast(rand()*1000  as int)% 36+1,1)
  + SUBSTRING(@abc,cast(rand()*1000  as int)% 36+1,1)
  + SUBSTRING(@abc,cast(rand()*1000  as int)% 36+1,1)
  + SUBSTRING(@abc,cast(rand()*1000  as int)% 36+1,1)
  + SUBSTRING(@abc,cast(rand()*1000  as int)% 36+1,1)
  + SUBSTRING(@abc,cast(rand()*1000  as int)% 36+1,1) code

I intentionally mixed styles to show variants.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

If your final value truly has to have a random element to it, then the only way to ensure uniqueness is to generate it, and then check your existing data to see if the generated value already exists.

You could do this with an AFTER INSERT/UPDATE TRIGGER.

Upvotes: 1

Related Questions