問題描述

我想在 sql server 中創(chuàng)建一個表并用數(shù)據(jù)(人們的信息)填充它.目標是讓每個人在第一行都有一個唯一的 ID，該 ID 必須以固定字母 GM 開頭，后跟 AZ 或 2-9數(shù)字，名字的首字母，姓氏的首字母和 AZ 或 2-9.

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.

推薦答案

注意:根據(jù)您所要求的邏輯，我認為您無法在不檢查表中不包含生成的值的情況下生成唯一值，唯一的方法是使用 NEWID() 函數(shù)生成 GUID，否則我認為總是存在重復風險

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

我不知道這是否是最好的方法，但我可以說它提供了預期的輸出.您可以創(chuàng)建此函數(shù)并使用它來生成標識符:

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

您可以將其用作以下內(nèi)容

And you can use it as the following

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

我將 RAND() 作為參數(shù)傳遞，因為它不能在函數(shù)中使用

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

這篇關(guān)于為 SQL Server 表中的用戶生成隨機令牌(唯一 ID)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！