declare @chv_sn char(40) set @chv_sn=cast(newid() as nvarchar(40)) set @chv_sn=replace (@chv_sn,'-','a') print @chv_sn 这样就可以了
--取得随机数的视图 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
declare @chv_sn char(40) set @chv_sn=cast(newid() as nvarchar(40)) set @chv_sn=replace (@chv_sn,'-','a')/* newid() 是唯一的,根据网卡MAC地址再加上当前时间生成。微软号称60年不会重复啊将字符串常量转换为如下形式(xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,其中每个 x 是 0-9 或 a-f 范围内的一个十六进制的数字)。例如,6F9619FF-8B86-D011-B42D-00C04FC964FF 即为有效的 uniqueidentifier 值*/
给分
select @chv_sn = right(str(rand(),8,6),2)
用rand函数
set @s=cast(newid() as nvarchar(50))
7271747(帅的拖网速) :转换成字符EECA0F15-1669-482F-B821-F93BDE771672也不合要求
set @chv_sn=cast(newid() as nvarchar(40))
set @chv_sn=replace (@chv_sn,'-','a')
print @chv_sn
这样就可以了
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
set @chv_sn=cast(newid() as nvarchar(40))
set @chv_sn=replace (@chv_sn,'-','a')/*
newid()
是唯一的,根据网卡MAC地址再加上当前时间生成。微软号称60年不会重复啊将字符串常量转换为如下形式(xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,其中每个 x 是 0-9 或 a-f 范围内的一个十六进制的数字)。例如,6F9619FF-8B86-D011-B42D-00C04FC964FF 即为有效的 uniqueidentifier 值*/