Números randomicos dentro de um período.

Seguindo a idéia dos últimos posts, essa rotina gera um número randomico dentro de um período estabelecido.

DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

SET @Lower = 800 --Limite inferior do periodo
SET @Upper = 999 --Limite superior do periodo
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND(CAST(NEWID() AS varbinary )) + @Lower), 0)
SELECT @Random


Um exemplo dessa aplicação é gerar números aleatórios de telefones:

DECLARE @Random VarChar(9)
DECLARE @Upper Decimal(9,0)
DECLARE @Lower Decimal(9,0)

SET @Lower = 30000000 --Limite inferior do periodo
SET @Upper = 99999999 --Limite superior do periodo
Select @Random = Stuff(Convert(VarChar(9), Convert(Decimal(9,0), ROUND(((99999999 - @Lower -1) * RAND(CAST(NEWID() AS varbinary)) + @Lower), 0))), 5, 0, '-')
SELECT @Random

Gerar CNPJ Válido

Por razões obvias de segurança de informação nós temos que trocar alguns dados (scramble) de informações confidenciais quando atualizamos as bases de desenvolvimento e homologação como dito nos post anterior. Outra informação necessãria é o CNPJ, essa rotina cria CNPJs validos e randomicos:



Declare @n Int, @n1 Int, @n2 Int, @n3 Int, @n4 Int, @n5 Int, @n6 Int, @n7 Int, @n8 Int, @n9 Int, @n10 Int, @n11 Int, @n12 Int
Declare @d1 Int, @d2 Int

Set @n = 9
Set @n1 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n2 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n3 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n4 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n5 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n6 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n7 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n8 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n9 = 0
Set @n10 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n11 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n12 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @d1 = @n12*2+@n11*3+@n10*4+@n9*5+@n8*6+@n7*7+@n6*8+@n5*9+@n4*2+@n3*3+@n2*4+@n1*5
Set @d1 = 11 - (@d1%11)
if (@d1>=10)
Set @d1 = 0
Set @d2 = @d1*2+@n12*3+@n11*4+@n10*5+@n9*6+@n8*7+@n7*8+@n6*9+@n5*2+@n4*3+@n3*4+@n2*5+@n1*6
Set @d2 = 11 - (@d2%11)
if (@d2>=10)
Set @d2 = 0


Select
Cast(@n1 as VarChar)+
Cast(@n2 as VarChar)+'.'+
Cast(@n3 as VarChar)+
Cast(@n4 as VarChar)+
Cast(@n5 as VarChar)+'.' +
Cast(@n6 as VarChar)+
Cast(@n7 as VarChar)+
Cast(@n8 as VarChar)+'/'+
Cast(@n9 as VarChar)+
Cast(@n10 as VarChar)+
Cast(@n11 as VarChar)+
Cast(@n12 as VarChar)+'-'+
Cast(@d1 as VarChar)+
Cast(@d2 as VarChar)

Gerar CPF Válido

Por razões obvias de segurança de informação nós temos que trocar alguns dados (scramble) de informações confidenciais quando atualizamos as bases de desenvolvimento e homologação. Uma delas é o CPF, essa rotina cria CPFs validos e randomicos:



Declare @n Int, @n1 Int, @n2 Int, @n3 Int, @n4 Int, @n5 Int, @n6 Int, @n7 Int, @n8 Int, @n9 Int
Declare @d1 Int, @d2 Int

Declare @NumeroCPFs Int

Create Table #TabelaCPF (sCPF VarChar(14))

Set @NumeroCPFs = 1000

While @NumeroCPFs > 0
Begin
Set @n = 9;
Set @n1 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n2 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n3 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n4 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n5 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n6 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n7 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n8 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n9 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @d1 = @n9*2+@n8*3+@n7*4+@n6*5+@n5*6+@n4*7+@n3*8+@n2*9+@n1*10;
Set @d1 = 11 - (@d1%11);
if (@d1>=10)
Set @d1 = 0
Set @d2 = @d1*2+@n9*3+@n8*4+@n7*5+@n6*6+@n5*7+@n4*8+@n3*9+@n2*10+@n1*11;
Set @d2 = 11 - ( @d2%11 );
if (@d2>=10)
Set @d2 = 0;

Insert Into #TabelaCPF Values (
Cast(@n1 as VarChar)+
Cast(@n2 as VarChar)+
Cast(@n3 as VarChar)+'.'+
Cast(@n4 as VarChar)+
Cast(@n5 as VarChar)+
Cast(@n6 as VarChar)+'.'+
Cast(@n7 as VarChar)+
Cast(@n8 as VarChar)+
Cast(@n9 as VarChar)+'-'+
Cast(@d1 as VarChar)+
Cast(@d2 as VarChar))

Set @NumeroCPFs = @NumeroCPFs - 1

End


Select * from #TabelaCPF

Drop Table #TabelaCPF