欢迎投稿

今日深度:

SQL 十位随机数(大小写字母+数据),

SQL 十位随机数(大小写字母+数据),


USE [TEST]
GO
/****** Object: UserDefinedFunction [dbo].[RANDTENNUMS] Script Date: 2019/7/23 15:40:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[RANDTENNUMS]()
returns varchar(10)
as
begin
DECLARE @i int
DECLARE @flag int
DECLARE @SerialNumber nvarchar(20)
DECLARE @RANDS Numeric(38,38)
--初始化设定
SET @i=1
SET @SerialNumber = ''

--生成10位随机码
WHILE @i<11
BEGIN
--设置随机,这个随机会选择字母(大小写)还是数字
SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]
SET @flag=ceiling(@RANDS *3)

IF @flag=1
BEGIN
--随机字母(大写去除大写的O)
SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]
WHILE char(65+ceiling(@RANDS * 25)) = 'O'
BEGIN
SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]
END
SELECT @SerialNumber=@SerialNumber+char(65+ceiling(@RANDS * 25))
END
else if @flag=2
begin
--随机字母(小写去除小写的o)
SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]
WHILE char(97+ceiling(@RANDS * 25)) = 'o'
BEGIN
SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]
END
select @SerialNumber=@SerialNumber+char(97+ceiling(@RANDS * 25))
end
else begin
--随机数字 1至9的随机数字(整数)
WHILE cast(ceiling(@RANDS * 9) as varchar(1)) = '0'
BEGIN
SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]
END
SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]
select @SerialNumber=@SerialNumber+cast(ceiling(@RANDS * 9) as varchar(1))
end

--进行下一个循环
SET @i=@i+1
END
RETURN @SerialNumber;
END

www.htsjk.Com true http://www.htsjk.com/Sql_Server/29798.html NewsArticle SQL 十位随机数(大小写字母+数据), USE [TEST] GO /****** Object: UserDefinedFunction [dbo].[RANDTENNUMS] Script Date: 2019/7/23 15:40:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE function [dbo].[RANDTEN...
相关文章
    暂无相关文章
评论暂时关闭