34,593
社区成员
发帖
与我相关
我的任务
分享
SELECT newid() [1],left(NEWID(),9)[2],RIGHT(NEWID(),9)[3]
1 2 3
------------------------------------ ------------------ ------------------
C77A22B9-858B-43DB-AC6A-D5ECE7B7B74C D25EED1E- 3B8515CDC
select left(replace(newid(),'-',''),9)
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
*/
/**
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 ---
select right(newid(),9)
,学习了!学习了!超级简单!我也想在这问大家一句!这函数为什么随机字母也只有A到F呢?函数本身就是这样吗?是专门为她的这个问题而设计 的吗?呵呵