Generate random tokens (unique id's) for users

I want to create a table in sql server and fill it up with data (people's info). The goal is to get every person a unique ID in the first row which has to start with fixed alphabets GM followed by A-Z or 2-9 numeric , initial of First name, Initial of Last name and A-Z or 2-9.

回答1:

Note: With the logic you are requesting i don't think you can produce Unique value without check that the table doesn't contains the generated value, the only way is to use NEWID() function that generate a GUID, else i think that there is always a duplication risk

I don't know if this is the best way to do that, but i can say that it gives the expected output. You can create this function and use it to generate the identifier:

ALTER FUNCTION dbo.CreateIdentifier
(
    -- Add the parameters for the function here
    @Firstname varchar(255),
    @Lastname varchar(255),
    @random1 decimal(18,10) ,
    @random2 decimal(18,10)
)
RETURNS varchar(10)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @S VARCHAR(10)
    DECLARE @S1 VARCHAR(1)
    DECLARE @S2 VARCHAR(1)
    DECLARE @len INT
    DECLARE @Random1Fixed INT
    DECLARE @Random2Fixed INT

    declare @alphabet varchar(36) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ23456789'



    SET @alphabet = REPLACE(@alphabet,LEFT(@Lastname,1),'')
    SET @alphabet = REPLACE(@alphabet,LEFT(@Firstname,1),'')

    SELECT @len = len(@alphabet)
    SET @Random1Fixed = ROUND(((@len - 1 -1) * @random1 + 1), 0)
    SET @Random2Fixed = ROUND(((@len - 1 -1) * @random2 + 1), 0)


    SET @S1 = substring(@alphabet, convert(int, @Random1Fixed ), 1)
    SET @S2 = substring(@alphabet, convert(int, @Random2Fixed), 1)

    SET @S = 'GM' + @S1 + LEFT(@Firstname,1) + LEFT(@Lastname,1) + @S2
    RETURN @S

END

And you can use it as the following

SELECT dbo.CreateIdentifier('John','Wills',RAND(),RAND())

I am passing RAND() as parameter because it cannot be used within a function

回答2:

There are a couple of ambiguities in your question, but hopefully by reading through this you will be able to fill in the blanks.

One question you may want to ask is if you truly want to go within the ID constraints you've provided. It may make a lot more sense to use an identity column (see: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property).

The exact syntax depends on which dialect of SQL you're working with. Take a look at this: https://www.w3schools.com/sql/sql_autoincrement.asp

For MS SQL server it would look something like this:

create table User (
   [UserID] int IDENTITY(1,1) PRIMARY KEY,
   [FirstName] varchar(50) not null,
   [LastName] varchar(50) not null
)

Let's say you populated this table with a few entries. If for some arbitrary requirement you needed "GM" in the front and the user's initials at the end, you could do something like:

select 'GM'+UserID+'substring(FirstName,0,1)+substring(LastName,0,1) from User

To answer your question more directly, it appears that you are asking for each user to have an ID that looks like GM[A-Z or 2-9][FirstInitial][LastInitial][A-Z or 2-9)]

This setup will give you a lot more of a headache, but it's certainly doable. Note that, as I understand your request, having just one character of A-Z/0-9 after GM and another character after the initials will give you a very limited # of possible unique ID's.

You did not give a max ID character length, so I'm assuming the entire length is fixed at 6 characters ("G","M","A-Z/2-9","F","L","A-Z/2-9"). If I'm misunderstanding you then it will definitely change the answer but hopefully this is still useful.

You could create a User table and a table that holds your possible characters like this:

create table UserTable  (
    ID varchar(50),
    FirstName varchar(50),
    LastName varchar(50)
)

create table PChars (
    Possibility varchar(1)
)

insert into PChars (Possibility) values
('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')

Then you can run the following over and over, note that John and Jane will never have the same ID even though they have the same initials. The while clause forbids it.

Also note that eventually this will break. If enough people with the same initials were in the table, the while clause could turn into an infinite loop. That's why this is kind of a bad idea!!!

delete UserTable /*this is just here so you can run this code over and over*/

/*John Doe*/
declare @FirstName varchar(50) = 'John'
declare @LastName varchar(50) = 'Doe'
declare @newID varchar(50) = ''
while(@newID = '' or @newID in (select distinct ID from UserTable))
  begin
    select @newID = 'GM'+(select top 1 Possibility from PChars order by newid())+substring(@FirstName,1,1)+substring(@LastName,1,1)+(select top 1 Possibility from PChars order by newid())
  end
insert into UserTable select @newID,@FirstName,@LastName

/*Jane Doe*/
select @FirstName = 'Jane'
select @LastName = 'Doe'
select @newID = ''
while(@newID = '' or @newID in (select distinct ID from UserTable))
  begin
    select @newID = 'GM'+(select top 1 Possibility from PChars order by newid())+substring(@FirstName,1,1)+substring(@LastName,1,1)+(select top 1 Possibility from PChars order by newid())
  end
insert into UserTable select @newID,@FirstName,@LastName

select * from UserTable

Here is some sample output:

+--------+-----------+----------+
|   ID   | FirstName | LastName |
+--------+-----------+----------+
| GMOJDF | John      | Doe      |
| GM2JDD | Jane      | Doe      |
+--------+-----------+----------+

So again, I feel like I am somewhat misinterpreting your question, but I think this gives you a good launching point.