2

Possible Duplicate:
Generating random strings with T-SQL

I need to generate a random string with alpha and numeric characters inside a trigger.

The string must have a length of 15 and uppercase.

Someone have an idea?

Community
  • 1
  • 1
Maxime
  • 357
  • 2
  • 10
  • 23

2 Answers2

6

This is far from an optimal solution, but it will work as specified:

select char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
      +char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
      +char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
      +char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)
      +char(rand()*26+65)+char(rand()*26+65)+char(rand()*26+65)

Example output:

WCZOVRCIRELAJUT
SWeko
  • 30,434
  • 10
  • 71
  • 106
  • If the length of 15 is set in stone, or if this is part of some test setup, than it's bulletproof. – SWeko Dec 19 '12 at 17:10
2

generate a GUID and take just the first 15 characters?

Generate a bunch of random numbers and translate to their ASCII values?

Limey
  • 2,642
  • 6
  • 37
  • 62
  • Not sure this is a good idea. The first 15 characters of a guid may not be so random... but then again, maybe they'll be random enough. – Joel Coehoorn Dec 19 '12 at 17:05
  • @JoelCoehoorn: Could maybe make it fancy and grab the first 6, then the last 5 with with the last 4 coming somewhere from the middle. – Limey Dec 19 '12 at 17:07
  • Part of the GUID is based on the MAC address would need to avoid that. – paparazzo Dec 19 '12 at 17:53
  • I'm pretty sure that GUID is hashed. There is no fixed part of GUID that would stay the same even on same MAC. – jumxozizi Oct 05 '17 at 10:18
  • I think `cast(cast(NEWID() as varbinary(max)) as varchar(max))` is what you are suggesting in this answer. One potential issue is that there will be random special characters like ASCII 0 (null). – jumxozizi Oct 05 '17 at 10:20