SQL 生成随机编号

最新推荐文章于 2023-01-05 18:18:00 发布

转载最新推荐文章于 2023-01-05 18:18:00 发布 · 595 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/myssh/archive/2009/08/28/1555530.html

文章标签：

#数据库

SQL-CODEALTER FUNCTION [dbo].[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
SET @r = LEFT(@r,@BHLen)
SET @r = @r+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120 ),':',''),'-',''),' ','')
RETURN(@r)
END
--执行示例
SELECT [dbo].[f_RANDBH](NULL)SELECT [dbo].[f_RANDBH](NULL)
--执行结果
DWihcunCUz20090828081450D大部分代码转载邹建SQL教程，自己也根据需要略有一点点改动！

转载于:https://www.cnblogs.com/myssh/archive/2009/08/28/1555530.html