七.生成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
*/

 

转载于:https://www.cnblogs.com/Dralion/p/3481609.html