生成纯字母随机编号的示例(大小写混合).sql

--取得随机数的视图
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(
           CASE WHEN SUBSTRING(re,1,1)>5 THEN 97 ELSE 65 end
           +(SUBSTRING(re,1,1)
           +SUBSTRING(re,2,1)
           +SUBSTRING(re,3,1))%26)
       +CHAR(
           CASE WHEN SUBSTRING(re,4,1)>5 THEN 97 ELSE 65 end
           +(SUBSTRING(re,4,1)
           +SUBSTRING(re,5,1)
           +SUBSTRING(re,6,1))%26)
   FROM v_RAND
   WHILE LEN(@r)<@BHLen
       SELECT @r=@r+CHAR(
               CASE WHEN SUBSTRING(re,1,1)>5 THEN 97 ELSE 65 end
               +(SUBSTRING(re,1,1)
               +SUBSTRING(re,2,1)
               +SUBSTRING(re,3,1))%26)
           +CHAR(
               CASE WHEN SUBSTRING(re,4,1)>5 THEN 97 ELSE 65 end
               +(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)
--结果: YZVBOj   LASCrhSO

chaunceyhao