小妹请教如何生成九位的随机密码！
今天我接到一个任务，要生成一个九位的随机密码！
要数字0-9 字母 A-F
随机取这里面的字母和数字。只要九位。
请问各位前辈应该如何去做！我确实没做过这方面开发！
[解决办法]
--参考:declare @maxNumber int, --随机数最大值 @minNumber int, --随机数最小值 @rows int --要取得的行数 select @maxNumber=10000, @minNumber=10000, @rows=10 set rowcount @rows select distinct '1234 4567 '+convert(varchar,ID)+' '+convert(varchar,ID)+' '+convert(varchar,ID)+' '+convert(varchar,ID) as 'B' from ( select convert(int,rand(checksum(newid()))*@minNumber) as ID from syscolumns,sysobjects )t set rowcount 0 -----------------------------------declare @num1 int ,@num2 int ,@num3 int ,@num4 int set @num1=rand(abs(convert(int,checksum(newid()))))*10000set @num2=rand(abs(convert(int,checksum(newid()))))*10000set @num3=rand(abs(convert(int,checksum(newid()))))*10000set @num4=rand(abs(convert(int,checksum(newid()))))*10000 select convert(varchar(100),@num1)+' '+convert(varchar(100),@num2)+' '+convert(varchar(100),@num3)+' '+convert(varchar(100),@num4)---------------------------------------declare @r1 numeric (15,0),@r2 numeric (15,0)SELECT @r1=RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) )*10000print @r1
[解决办法]
--把2楼的写成个函数--见newid视图create view v_newid as select [id] = newid()--建函数，获取随即9位数据+字母密码create function f_getRandPassword9()returns varchar(9)asbegin declare @result varchar(9) select @result = right(id,9) from v_newid; return @result;end--查询结果select dbo.f_getRandPassword9()--结果/*---------357771BF6(1 行受影响)*/
[解决办法]
select right(newid(),9) 最好，而且简单
[解决办法]
可能重复的几率有多大呀。 很少遇到重复，不信你试试。
[解决办法]
}
return ss随机数字;
}
[解决办法]
declare @var varchar(16)
declare @in int
declare @result varchar(9)
declare @i int
set @i=0
set @result=''
set @var='0123456789ABCDEF'
while @i<9
begin
set @in=ceiling(rand()*16)
set @result=@result+substring(@var,@in,1)
set @i=@i+1
end
print @result
[解决办法]
/**use masterSELECT name, dbo.fn_RandomPassword(8,8) as UserPassword FROM sysusersdbo.fn_RandomPassword(最小长度,最大长度)**/--- Start vwRand --- SET quoted_identifier ON GO SET ansi_nulls ON GO IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = Object_id(N'[dbo].[vwRand]') and OBJECTPROPERTY(id, N'IsView') = 1) DROP VIEW [dbo].[vwrand] GO --created by Rick Toner on 03/16/2007 --updated by Rick Toner on 03/16/2007 CREATE VIEW dbo.vwrand AS SELECT Rand() AS R GO SET quoted_identifier off GO SET ansi_nulls ON GO --Uncomment the below line if you need to implement security --GRANT SELECT ON [vwRand] TO [UserAccountOrDataseRole] --- End vwRand --- --- Start fn_Rand --- SET quoted_identifier ON GO SET ansi_nulls ON GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[fn_Rand]') AND xtype IN (N'FN',N'IF',N'TF')) DROP FUNCTION [dbo].[fn_rand] GO --created by Rick Toner on 03/16/2007 --updated by Rick Toner on 03/16/2007 CREATE FUNCTION fn_rand( ) RETURNS FLOAT AS BEGIN RETURN (SELECT r FROM vwrand) END GO SET quoted_identifier off GO SET ansi_nulls ON GO --Uncomment the below line if you need to implement security --GRANT EXECUTE ON [fn_Rand] TO [UserAccountOrDataseRole] --- End fn_Rand --- --- Start fn_RandomPassword --- SET quoted_identifier ON GO SET ansi_nulls ON GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[fn_RandomPassword]') AND xtype IN (N'FN',N'IF',N'TF')) DROP FUNCTION [dbo].[fn_randompassword] GO --created by Rick Toner on 03/16/2007 --updated by Rick Toner on 03/16/2007 CREATE FUNCTION fn_randompassword (@MinLength SMALLINT = 5, @MaxLength SMALLINT = 8) RETURNS VARCHAR(100) AS BEGIN DECLARE @Password VARCHAR(30) DECLARE @Length SMALLINT DECLARE @Position SMALLINT DECLARE @Characters VARCHAR(55) DECLARE @LetterPosition INT DECLARE @Letter CHAR(1) SET @Characters = 'aeubcdfghjklmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ23456789' SET @Password = '' -- determine length SET @Length = @MinLength + Round(dbo.Fn_rand() * (@MaxLength - @MinLength),0,0) SET @Position = 1 WHILE @Position <= @Length BEGIN BEGIN BEGIN SET @LetterPosition = CONVERT(INT,Round((dbo.Fn_rand() * (Len(@Characters) - 1)),0,0),1) + 1 SET @Letter = Substring(@Characters,@LetterPosition,1) SET @Password = @Password + @Letter END END SET @Position = @Position + 1 -- incriment counter END -- return password RETURN @password END GO SET quoted_identifier off GO SET ansi_nulls ON GO --Uncomment the below line if you need to implement security --GRANT EXECUTE ON [fn_RandomPassword] TO [UserAccountOrDataseRole] --- End fn_RandomPassword ---
[解决办法]
http://www.sqlservercentral.com/articles/SQL+Puzzles/2878/CREATE PROCEDURE dbo.uspCreatePassword( @UpperCaseItems SMALLINT--指定含有的大写个数 , @LowerCaseItems SMALLINT--指定含有的小写个数 , @NumberItems SMALLINT--指定含有的数字个数 , @SpecialItems SMALLINT)--指定含有的特殊字符个数 AS SET NOCOUNT ON DECLARE @UpperCase VARCHAR(26) , @LowerCase VARCHAR(26) , @Numbers VARCHAR(10) , @Special VARCHAR(13) , @Temp VARCHAR(8000) , @Password VARCHAR(8000) , @i SMALLINT , @c VARCHAR(1) , @v TINYINT -- Set the default items in each group of charactersSELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' , @LowerCase = 'abcdefghijklmnopqrstuvwxyz' , @Numbers = '0123456789' , @Special = '!@#$%&*()_+-=' , @Temp = '' , @Password = '' -- Enforce some limits on the length of the passwordIF @UpperCaseItems > 20 SET @UpperCaseItems = 20 IF @LowerCaseItems > 20 SET @LowerCaseItems = 20 IF @NumberItems > 20 SET @NumberItems = 20 IF @SpecialItems > 20 SET @SpecialItems = 20 -- Get the Upper Case ItemsSET @i = ABS(@UpperCaseItems) WHILE @i > 0 AND LEN(@UpperCase) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1 , @c = SUBSTRING(@UpperCase, @v, 1) , @UpperCase = CASE WHEN @UpperCaseItems < 0 THEN STUFF(@UpperCase, @v, 1, '') ELSE @UpperCase END , @Temp = @Temp + @c , @i = @i - 1 -- Get the Lower Case ItemsSET @i = ABS(@LowerCaseItems) WHILE @i > 0 AND LEN(@LowerCase) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1 , @c = SUBSTRING(@LowerCase, @v, 1) , @LowerCase = CASE WHEN @LowerCaseItems < 0 THEN STUFF(@LowerCase, @v, 1, '') ELSE @LowerCase END , @Temp = @Temp + @c , @i = @i - 1 -- Get the Number ItemsSET @i = ABS(@NumberItems) WHILE @i > 0 AND LEN(@Numbers) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1 , @c = SUBSTRING(@Numbers, @v, 1) , @Numbers = CASE WHEN @NumberItems < 0 THEN STUFF(@Numbers, @v, 1, '') ELSE @Numbers END , @Temp = @Temp + @c , @i = @i - 1 -- Get the Special ItemsSET @i = ABS(@SpecialItems) WHILE @i > 0 AND LEN(@Special) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1 , @c = SUBSTRING(@Special, @v, 1) , @Special = CASE WHEN @SpecialItems < 0 THEN STUFF(@Special, @v, 1, '') ELSE @Special END , @Temp = @Temp + @c , @i = @i - 1 -- Scramble the order of the selected itemsWHILE LEN(@Temp) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1 , @Password = @Password + SUBSTRING(@Temp, @v, 1) , @Temp = STUFF(@Temp, @v, 1, '') SELECT @Password/*--exec uspCreatePassword 2,2,2,2----------MT&4g(5jexec uspCreatePassword 2,2,4,0------------38Io84Vw*/
[解决办法]
