0

I have a table with a list of invitations. Each invitation should contain an unique InviteCode. Example: 4BF8

I want to generate a dynamically a unique InviteCode. I mean, that that string does not exist in the table.

I have been using this SQL sentence to generate the random string:

select LEFT(newId(), 8)

I've thinking using something like: 

select LEFT(newId(), 4) as myNewInviteCode WHERE  NOT  EXISTS 
(SELECT InviteCode from  Userconnections WHERE InviteCode = myInviteCode)

Any idea? suggestion?

Thanks!

daronwolff
  • 1,994
  • 21
  • 18
  • Does it *have* to be a 4 character alphanumerical value? If not, why not use `NEWID()` to generate a Globally Unique ID? – Thom A Apr 30 '19 at 19:17
  • Are you aware of the problem that *four-letter words* can cause? I suggest that you create a table with all possible values and mark them down when you've used them. – Luis Cazares Apr 30 '19 at 19:22
  • Yes, It will be sent via e-mail and used to register in the website, I want to keep it short and simple – daronwolff Apr 30 '19 at 19:22
  • It's a bad idea to use LEFT(NEWID(),4) – Alex M Apr 30 '19 at 19:23
  • Does the invite code need to be random or can you just create an incrementally coded structure? ie: AAAA, AAAB, AAAC, etc...(4 random digits isn't much more secure than linear values) – level3looper Apr 30 '19 at 19:23
  • It has to be random. I'm open to new Ideas. Thanks guys – daronwolff Apr 30 '19 at 19:23
  • If this is to be used on a website, and needs to be unique, you want far more than 4 characters. People **will** try other combinations, and that will not be good. – Thom A Apr 30 '19 at 19:24
  • 1
    If the Userconnections table is small, that seems fine. You could also (probably should) have a unique constraint on the column, so could just try inserting and retry if it it fails. If it always has to have a letter, and not potentially be 4-numbers, how about "select CHAR(CAST(RAND()*25+1 AS SMALLINT)+ASCII('A'))+left(newId(), 3)"? I think 4 characters is fine, as long as you block access to someone trying random combinations after a reasonably small number of attempts. – Ian McGowan Apr 30 '19 at 19:24
  • 1
    Re-create the table with an identity column and live happily ever after. – Alex M Apr 30 '19 at 19:25
  • @AlexM - too easy to guess the next in the sequence, if this is being used as a pseudo-password, where user A is entering something for user B to use later. Kind of agree that a full guid in a link might be easier all around, But if it's communicated verbally that gets tough :-) – Ian McGowan Apr 30 '19 at 19:28
  • Thanks Guys! I'll keep with the suggestion of using full GUID in a link! – daronwolff Apr 30 '19 at 19:31

0 Answers0