問題描述
有沒有辦法生成隨機base36 標識符SQL Server 中定義的字符數(shù)?
Is there a way of generating random base36 identifiers with a defined character count in SQL Server?
我搜索并發(fā)現(xiàn)了許多將 base 36 轉(zhuǎn)換為 int 以及將其轉(zhuǎn)換為 int 的示例,但不是用于隨機生成唯一 ID 的示例.
I have searched and found many examples of converting base 36 to int and vice versa, but not for randomly generating unique IDs.
推薦答案
這個解決方案有點冗長但有效并且可以輕松適應各種需求.以下是一些示例輸出:
This solution a bit verbose but works and can easily be adapted to a variety of requirements. Here's some sample output:
aapx0k k4fdbb vzbl5x
8vr1bs gbix1q g5kctv
he6e50 m9j0m0 2vz53l
yw72hs hgbo5h 3oen9v
6t4q75 337670 5sf3h4
yqr35s xoh4hh tc0wtf
w7trkj lnnpdk zk2ln1
1gt7qr l6m72n ja5kvm
kg6f9y 6t3b7a ujfr0i
2jatgo 0yv8rv wvbjfa
請注意,您需要創(chuàng)建一個視圖來包裝 UDF 中不允許使用的 RAND.所以這個解決方案需要兩個 db 對象,一個視圖和一個 udf.
Note that you need to create a view to wrap the use of RAND which is not allowed inside a UDF. So this solution needs two db objects, a view and a udf.
CREATE VIEW ViewRandInt AS (SELECT RAND() * 36 as RandInt)
GO
CREATE FUNCTION GetRandomBase36Id
(
@charCount AS INT
)
RETURNS VARCHAR(50) AS BEGIN
DECLARE @characters CHAR(36),
@result VARCHAR(MAX),
@counter INT,
@randNum INT
SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',
@result = '',
@counter = 0;
WHILE @counter < @charCount
BEGIN
SELECT @randNum = RandInt FROM ViewRandInt
SET @result = @result + SUBSTRING(@characters, @randNum+1, 1)
SET @counter = @counter + 1
END
RETURN @result;
END
-- Test:
SELECT dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
這篇關(guān)于如何生成隨機 Base36 ID的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網(wǎng)!