sql 生成随机字符串

 生成三位随机字母+12位数字

declare  @CardCode varchar(50),@str varchar(50), @c int;
select @CardCode=abs(CHECKSUM(NEWID()))
set @c=12-LEN(@CardCode);
select @str= FLOOR(rand()*POWER(10,@c))
set @CardCode=@CardCode+@str

declare   @sql   nvarchar(400) ,@name varchar(50) ;  

 select @name=  char(  cONVERT(NVARCHAR,CONVERT(INT,26*rand())+97) )+    
CHAR(  CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+97) )+    
CHAR(  CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+97) )

  select @name+CAST( @CardCode as nvarchar(50))

 

下面这个是生成大写字母的

declare   @sql   nvarchar(400) ,@name varchar(50) ;  
 select @name=  char(  cONVERT(NVARCHAR,CONVERT(INT,26*rand())+65) )+    
CHAR(  CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+65) )+    
CHAR(  CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+65) )
select @name

 生成随机字母+数字的存储过程

ALTER proc [dbo].[randStr]
(
@digitalLen int,  --数字长度
@letterLen int, --字母长度
@IsCapital bit  --大写,1=是 ,0=否
)
--RETURNs varchar(100)
as

begin 

    declare  @CardCode varchar(50),@str varchar(50), @c int,@name varchar(50);
    if(@IsCapital=0)
        begin
            
            select @CardCode=abs(CHECKSUM(NEWID()))
            
            if(@digitalLen>LEN(@CardCode))
                begin
                    set @c=@digitalLen-LEN(@CardCode);
                    select @str= FLOOR(rand()*POWER(10,@c))
                    set @CardCode=@CardCode+@str
                end
            else
                begin                     
                    set @CardCode=left(@CardCode,@digitalLen)
                end    
                
             
            select @name=  char(  cONVERT(NVARCHAR,CONVERT(INT,26*rand())+97) )+    
            CHAR(  CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+97) )+    
            CHAR(  CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+97) )
                
            set @CardCode= @name+CAST( @CardCode as nvarchar(50))
        end
    else
        begin
                
            select @CardCode=abs(CHECKSUM(NEWID()))
            
            if(@digitalLen>LEN(@CardCode))
                begin
                    set @c=@digitalLen-LEN(@CardCode);
                    select @str= FLOOR(rand()*POWER(10,@c))
                    set @CardCode=@CardCode+@str
                end
            else
                begin                     
                    set @CardCode=left(@CardCode,@digitalLen)
                end    
                
             
            select @name=  char(  cONVERT(NVARCHAR,CONVERT(INT,26*rand())+65) )+    
            CHAR(  CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+65) )+    
            CHAR(  CONVERT(NVARCHAR,CONVERT(INT,rand()*26)+65) )
                
            set @CardCode= @name+CAST( @CardCode as nvarchar(50))
        end
    select  @CardCode;

end

 

转载于:https://www.cnblogs.com/yangjinwang/p/9179300.html