--产生随机数的几种办法:
SELECT CHAR(65 + CEILING(RAND() * 25))
SELECT RIGHT(RAND(), 1)
SELECT CHAR(65 + CEILING(RAND() * 25))
SELECT UPPER(CHAR(65 + CEILING(RAND() * 25)))
SELECT RIGHT(RAND(), 1)
SELECT LOWER(CHAR(65 + CEILING(RAND() * 25))) + UPPER(CHAR(65
+ CEILING(RAND()
* 25)))
+ RIGHT(RAND(), 1)
-----------------------------------------------------------------
--通过函数返回指定长度的随机数
ALTER VIEW v_yhj_rand
AS
SELECT RAND() AS v_yhj_randA;
GO
CREATE FUNCTION fn_gen_Round ( @len INT -- 随机数长度
)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @retValue DATETIME;
--Set @retValue = '2016-07-01 08:00'
/*
select char(65+ceiling(rand()*25)) --随机字母(大写)
select char(97+ceiling(rand()*25)) --随机字母(小写)
select cast(ceiling(rand()*9) as varchar(1)) --随机数字 1至9的随机数字(整数)
*/
DECLARE @i INT;
DECLARE @flag INT;
DECLARE @ra VARCHAR(20); ---最后会生成的随机码
DECLARE @ratemp VARCHAR(20); ---最后会生成的随机码
--初始化设定
SET @i = 1;
SET @ra = '';
--生成指定位数位随机码
WHILE @i < CAST(@len AS NVARCHAR(2)) + 1
BEGIN
--设置随机,这个随机会选择字母(大小写)还是数字
--set @flag=ceiling(v_yhj_randA() * 3)
SELECT @flag = CEILING(v_yhj_randA * 3)
FROM v_yhj_rand;
IF @flag = 1
BEGIN
--随机字母(大写)
SELECT @ratemp = CHAR(65 + CEILING(v_yhj_randA * 25))
FROM v_yhj_rand;
-- select @ra=@ra+char(65+ceiling(rand()*25))
SELECT @ra = @ra + @ratemp;
END;
ELSE
IF @flag = 2
BEGIN
--随机字母(小写)
SELECT @ratemp = CHAR(97 + CEILING(v_yhj_randA
* 25))
FROM v_yhj_rand;
--select @ra=@ra+char(97+ceiling(rand()*25))
SELECT @ra = @ra + @ratemp;
END;
ELSE
BEGIN
--随机数字 1至9的随机数字(整数)
--select @ra=@ra+cast(ceiling(rand()*9) as varchar(1))
SELECT @ratemp = CAST(CEILING(v_yhj_randA * 9) AS VARCHAR(1))
FROM v_yhj_rand;
SELECT @ra = @ra + @ratemp;
END;
SET @i = @i + 1;
END;
--print('随机码:'+@ra)
RETURN @ra;
END;
--SELECT dbo.fn_gen_Round(12) --测试函数效果
-----------------------------------------------------------------
--通过存储过程计算 随机数
CREATE PROCEDURE [dbo].p_gen_Round @len int -- 随机数长度
AS ----
BEGIN
/*
select char(65+ceiling(rand()*25)) --随机字母(大写)
select char(97+ceiling(rand()*25)) --随机字母(小写)
select cast(ceiling(rand()*9) as varchar(1)) --随机数字 1至9的随机数字(整数)
*/
DECLARE @i int
DECLARE @flag int
DECLARE @ra varchar(20) ---最后会生成的随机码
--初始化设定
SET @i = 1
SET @ra = ''
--生成指定位数位随机码
WHILE @i < CAST(@len AS NVARCHAR(2)) + 1
BEGIN
--设置随机,这个随机会选择字母(大小写)还是数字
SET @flag = CEILING(RAND() * 3)
IF @flag = 1
BEGIN
--随机字母(大写)
SELECT @ra = @ra + CHAR(65 + CEILING(RAND() * 25))
END
ELSE
IF @flag = 2
BEGIN
--随机字母(小写)
SELECT @ra = @ra + CHAR(97 + CEILING(RAND() * 25))
END
ELSE
BEGIN
--随机数字 1至9的随机数字(整数)
SELECT @ra = @ra
+ CAST(CEILING(RAND() * 9) AS varchar(1))
END
SET @i = @i + 1
END
PRINT ( '随机码:' + @ra )
--测试结果
/*
随机码:S33V95xMbw89
*/
END
--EXEC p_gen_Round 4 测试
-----------------------------------------------------