小妹请教如何生成九位的随机密码！

[wangtiantian23]

今天我接到一个任务，要生成一个九位的随机密码！

要数字0-9 字母 A-F

随机取这里面的字母和数字。只要九位。

请问各位前辈应该如何去做！我确实没做过这方面开发！

[bestboy1]

学习学习。

[justqken]

学习 学习

[rfq]

select newid()

[幸运的意外]

select right(newid(),9)
select left(newid(), 9)

[我是小数位]

select right(newid(),9)

,学习了！学习了！超级简单！我也想在这问大家一句！这函数为什么随机字母也只有A到F呢？函数本身就是这样吗？是专门为她的这个问题而设计 的吗？呵呵

[yujunlin32167]

[Quote=引用 27 楼 cxj0062008 的回复:]
SQL code
select right(newid(),9)
,学习了！学习了！超级简单！我也想在这问大家一句！这函数为什么随机字母也只有A到F呢？函数本身就是这样吗？是专门为她的这个问题而设计 的吗？呵呵
[/Quote]
随机生成函数应该是生成16位数的函数...所以只有A-F字母...

[数字化从业者老高]

newid()

[sych888]

select LEFT(newid(),9);

[我是小数位]

[Quote=引用 28 楼 yujunlin32167 的回复:]
引用 27 楼 cxj0062008 的回复:
SQL code
select right(newid(),9)
,学习了！学习了！超级简单！我也想在这问大家一句！这函数为什么随机字母也只有A到F呢？函数本身就是这样吗？是专门为她的这个问题而设计 的吗？呵呵

随机生成函数应该是生成16位数的函数...所以只有A-F字母[/Quote]
...哦，是的哦，应该是生成产生16进制的数的随机函数！谢谢了！一定是的！

[东那个升]

/**
use master

SELECT name, dbo.fn_RandomPassword(8,8) as UserPassword
    FROM sysusers

dbo.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 characters
SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    ,    @LowerCase = 'abcdefghijklmnopqrstuvwxyz'
	 ,    @Numbers = '0123456789'
	 ,    @Special = '!@#$%&*()_+-='
	 ,    @Temp = ''
	 ,    @Password = '' 
-- Enforce some limits on the length of the password
IF @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 Items
SET @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 Items
SET @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 Items
SET @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 Items
SET @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 items
WHILE 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(5j


exec uspCreatePassword 2,2,4,0

------------
38Io84Vw

*/

[wangtiantian23]

LEFT(newid(),9);
right(newid(),9);
两者有什么区别！

[cailee]

select left(replace(newid(),'-',''),9)

[东那个升]

[Quote=引用 34 楼 wangtiantian23 的回复:]
LEFT(newid(),9);
right(newid(),9);
两者有什么区别！
[/Quote]

一个从左边开始去，一个右边

[yt122656908]

一个是从小到大排序，一个是从大到小排序。我觉得是这个样子的。顺便问下newid（）是一个基于16进制随机取值的函数对吗？

[yt122656908]

如果使用left的话只能取8个随机数！

[好奇都是要学的]

都这么有才

[老黎]

我觉得东升哥是神！

[永生天地]

[Quote=引用 34 楼 wangtiantian23 的回复:]
LEFT(newid(),9);
right(newid(),9);
两者有什么区别！
[/Quote]
查一下就知道了

SELECT newid() [1],left(NEWID(),9)[2],RIGHT(NEWID(),9)[3]

1                                    2                  3                  
------------------------------------ ------------------ ------------------ 
C77A22B9-858B-43DB-AC6A-D5ECE7B7B74C D25EED1E-          3B8515CDC

[卡卡_苏米]

随机函数生成的话 应该很简单吧