5.2.3 生成纯字母随机编号的示例(仅大小或者小写).sql

来自「sqlserver 数据库编程的绝好脚本」· SQL 代码 · 共 42 行

--取得随机数的视图
CREATE VIEW v_RAND
AS
SELECT re=STUFF(RAND(),1,2,'')
GO

--生成随机编号的函数
CREATE FUNCTION f_RANDBH(@BHLen int)
RETURNS varchar(50)
AS
BEGIN
	DECLARE @r varchar(50)
	IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50)
		SET @BHLen=10

	SELECT @r=CHAR(65
			+(SUBSTRING(re,1,1)
			+SUBSTRING(re,2,1)
			+SUBSTRING(re,3,1))%26)
		+CHAR(65
			+(SUBSTRING(re,4,1)
			+SUBSTRING(re,5,1)
			+SUBSTRING(re,6,1))%26)
	FROM v_RAND
	WHILE LEN(@r)<@BHLen
		SELECT @r=@r+CHAR(65
				+(SUBSTRING(re,1,1)
				+SUBSTRING(re,2,1)
				+SUBSTRING(re,3,1))%26)
			+CHAR(65
				+(SUBSTRING(re,4,1)
				+SUBSTRING(re,5,1)
				+SUBSTRING(re,6,1))%26)
		FROM v_RAND
	RETURN(LEFT(@r,@BHLen))
END
GO

--调用
SELECT dbo.f_RANDBH(6),dbo.f_RANDBH(8)
--结果: UJXIJD  PAPGTQUX