SQL语言随机生成字符串的几种方法

2025-04-04 17:56:23

1、1.利用newid()产生的uniqueidentifier都是随机且唯一的;示例:declare @string nvarchar(100);set @string =cast(newid() as nvarchar(100));select @string;go

2、2.利用rand()生成随机数字串;示例:declare @string nvarchar(100);set @string = right(str(rand(),8,6),2);select @string;go

3、3.利用rand()生成6位随机字符串;示例:declare @sql nvarchar(400)select @sql= &垆杪屑丝#39;select char( '+cONVERT(NVARCHAR,CONVERT(INT,26*rand())+97)+ ')+CHAR( '+CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+97)+ ')+CHAR( '+CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+97)+ ')+CHAR( '+CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+97)+ ')+CHAR( '+CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+97)+ ')+CHAR( '+CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+97)+ ') 'print @sqlEXEC(@sql)goDECLARE @Below intDECLARE @Up intSELECT @Below=65,@Up=90SELECT CHAR(CAST(RAND()*(@Up-@Below)+@Below AS decimal(38,0))) +CHAR(CAST(RAND()*(@Up-@Below)+@Below AS decimal(38,0))) +CHAR(CAST(RAND()*(@Up-@Below)+@Below AS decimal(38,0))) +CHAR(CAST(RAND()*(@Up-@Below)+@Below AS decimal(38,0))) +CHAR(CAST(RAND()*(@Up-@Below)+@Below AS decimal(38,0))) +CHAR(CAST(RAND()*(@Up-@Below)+@Below AS decimal(38,0)))goSELECT CHAR(CONVERT(INT,rand()*26)+(CASE WHEN RAND()*2 > 1 THEN 97 ELSE 65 END))+CHAR(CONVERT(INT,rand()*26)+(CASE WHEN RAND()*2 > 1 THEN 97 ELSE 65 END))+CHAR(CONVERT(INT,rand()*26)+(CASE WHEN RAND()*2 > 1 THEN 97 ELSE 65 END))+CHAR(CONVERT(INT,rand()*26)+(CASE WHEN RAND()*2 > 1 THEN 97 ELSE 65 END))+CHAR(CONVERT(INT,rand()*26)+(CASE WHEN RAND()*2 > 1 THEN 97 ELSE 65 END))+CHAR(CONVERT(INT,rand()*26)+(CASE WHEN RAND()*2 > 1 THEN 97 ELSE 65 END))go

SQL语言随机生成字符串的几种方法

4、4.编写可以产生随机字符串的存储过程;示例:--*********************CREATE VIEW V_RAND AS SELECT RAND1 租涫疼迟= CONVERT(INT,RAND()*26),RAND2 = RAND()*2 GO CREATE FUNCTION DBO.f_GetRandNum(@LEN INT,@FLAG INT)RETURNS NVARCHAR(100)AS--@LEN 输出字符的长度--@FLAG 返回值包含字符 1:大写字母 2:小写字母 3:大小写字母混合BEGIN DECLARE @SQL NVARCHAR(100),@RAND INT SELECT @SQL = ' ' IF @LEN>100 SET @LEN = 100 WHILE @LEN>0 BEGIN SELECT @RAND = RAND1 +(CASE @FLAG WHEN 1 THEN 65 WHEN 2 THEN 97 ELSE(CASE WHEN RAND2 > 1 THEN 97 ELSE 65 END) END) FROM V_RAND SELECT @SQL=@SQL + CHAR(@RAND),@LEN = @LEN - 1 END RETURN @SQLENDGO--调用存储过程 SELECT DBO.f_GetRandNum(7,2)

声明:本网站引用、摘录或转载内容仅供网站访问者交流或参考,不代表本站立场,如存在版权或非法内容,请联系站长删除,联系邮箱:site.kefu@qq.com。
猜你喜欢