SQL產生n位隨機字串，sql產生n

SQL產生n位隨機字串，sql產生n

--1、藉助newid()  go  --建立視圖(因為在函數中無法直接使用newid())  create view vnewid  as  select newid() N'MacoId';  go  --建立函數 create function getrandstr(@n int)  returns varchar(max)  as  begin      declare @i int      set @i=ceiling(@n/32.00)      declare @j int      set @j=0      declare @k varchar(max)      set @k=''      while @j<@i      begin      select @k=@k+replace(cast(MacoId as varchar(36)),'-','') from vnewid      set @j=@j+1      end      set @k=substring(@k,1,@n)  return @k  end     --測試樣本 select dbo.getrandstr(75)  --運行結果 /*  D185504AD09C4D5796F7016983E67414CEE25162EA9F43D195D43328A4CF01AC7C586521D8E  */     --我們可以發現結果中的字母都是大寫的，或是都是小寫。 --換種方法來寫下： go  --建立函數 create function [dbo].[m_rand](@mycount int)  returns nvarchar(2000)  as  begin         declare @maco_wang table (id varchar(1))         declare @maco_number int,@number int;         declare @my_one nvarchar(max),@my_two nvarchar(max)         set @my_one='';set @maco_number=0; set @number =48;         while (@number>=48 and @number<=57) or (@number>=65 and @number<=90) or (@number>=97 and @number<=122)          begin             insert into @maco_wang select char(@number)             set @number=@number+1;             if(@number=58)             begin                set @number=65                       end             if(@number=91)             begin                     set @number=97                end         end         while @maco_number<@mycount         begin                select @my_two=id from @maco_wang                order by (select MacoId from dbo.m_macoview);                set @my_one=@my_two+@my_one;                set @maco_number=@maco_number+1;         end      return @my_one  end  --測試案例 select [dbo].[m_rand](75)  --運行結果 /*  5nN0w4o4VOkjacB5so2uvCuw2ZRrnBhxEi4IcsEOHzBbStKmR1p8ASH4N4XaxhDoDEtkX8bZ0CR  */ 

SQL語言隨機產生字串的幾種方法

1.利用newid()產生的uniqueidentifier都是隨機且唯一的：declare @string nvarchar(100);set @string =cast(newid() as nvarchar(100));select @string;go2.利用rand（）產生隨機數字串：declare @string nvarchar(100);set @string = right(str(rand(),8,6),2);select @string;go3.利用rand（）產生6位隨機字串：declare @sql nvarchar(400) select @sql= '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 @sql EXEC(@sql) goDECLARE @Below int DECLARE @Up int SELECT @Below=65,@Up=90 SELECT 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......餘下全文>>
 
SQL2000，怎隨機字串

1.利用newid()產生的uniqueidentifier都是隨機且唯一的：
declare @string nvarchar(100);
set @string =cast(newid() as nvarchar(100));
select @string;
go
2.利用rand（）產生隨機數字串：
declare @string nvarchar(100);
set @string = right(str(rand(),8,6),2);
select @string;
go

3.利用rand（）產生6位隨機字串：
declare @sql nvarchar(400)
select @sql= '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 @sql
EXEC(@sql)
go

DECLARE @Below int
DECLARE @Up int
SELECT @Below=65,@Up=90
SELECT 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)))

go
SELECT 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 &gt......餘下全文>>