Sql Server 函数生成随机字符串

无主题博客 » 数据库 » SQLServer » Sql Server 函数生成随机字符串

利用sql自定义函数,生成指定长度随机字符串

USE [TEST_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[vw_rand]
as
select Rand() as re
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_GeneratePassword](@Length int)
returns varchar(100)
begin
 DECLARE @RandomID varchar(32);
 DECLARE @counter smallint;
 DECLARE @RandomNumber float;
 DECLARE @RandomNumberInt tinyint;
 DECLARE @CurrentCharacter varchar(1);
 DECLARE @ValidCharacters varchar(255);
 SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
 DECLARE @ValidCharactersLength int;
 SET @ValidCharactersLength = len(@ValidCharacters);
 SET @CurrentCharacter = '';
 SET @RandomNumber = 0;
 SET @RandomNumberInt = 0;
 SET @RandomID = '';
 --SET NOCOUNT ON;
 SET @counter = 1;
 WHILE @counter < (@Length + 1)
 BEGIN
 select @RandomNumber = re from v_rand;
 SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength -1) * @RandomNumber + 1));
 SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1);
 SET @counter = @counter + 1;
 SET @RandomID = @RandomID + @CurrentCharacter;
 END
 
 Return @RandomID;
end

发表评论

电子邮件地址不会被公开。 必填项已用*标注