Home » Random Data Generation

Random Data Generation

Sometimes we will be needing to generate random number, alphabets for various development, security purposes.
Random number generator which should be put in Do …While statement:

select
substring(ch,convert(int,rand()*len(ch)-1),1)+
substring(ch,convert(int,rand()*len(ch)-2),1)+
substring(ch,convert(int,rand()*len(ch)-3),1)+
substring(ch,convert(int,rand()*len(ch)-4),1)+
substring(ch,convert(int,rand()*len(ch)-5),1)+
substring(ch,convert(int,rand()*len(ch)-6),1)+
substring(ch,convert(int,rand()*len(ch)-7),1)+
substring(ch,convert(int,rand()*len(ch)-8),1)+
substring(ch,convert(int,rand()*len(ch)-9),1)
from
(select ch =
replicate('0123456789',12)) a

The above code can be changed to any len by tweaking the length.

Random alphabet generators which can be used in Do.. while statement:

select
substring(ch,convert(int,rand()*len(ch)-1),1)+
substring(ch,convert(int,rand()*len(ch)-2),1)+
substring(ch,convert(int,rand()*len(ch)-3),1)+
substring(ch,convert(int,rand()*len(ch)-4),1)+
substring(ch,convert(int,rand()*len(ch)-5),1)+
substring(ch,convert(int,rand()*len(ch)-6),1)+
substring(ch,convert(int,rand()*len(ch)-7),1)+
substring(ch,convert(int,rand()*len(ch)-8),1)+
substring(ch,convert(int,rand()*len(ch)-9),1)
from
(select ch =
replicate('ABCDEFGHJKLMNPQURSUVWXYZ',9)+
replicate('abcdefghjkmnpqursuvwxyz',9)) a

The above code can be changed to any len by tweaking the length.

Random Date generators which can be used in Do.. while statement:

select
DateAdd(yy, -1 * cast(substring(ch,convert(int,rand()*len(ch)-1),1) as int), birthdate )
from
(select ch =
replicate('123456789',8)) a

Example using all three of them:

declare @TempTable table (Ind int identity(1,1), Empid varchar(10), fname varchar(10), birthdate datetime )
insert into @TempTable
select '874564567', 'John', '10/12/1984' union
select '874564565', 'Smith', '09/12/1985' union
select '874564562', 'George', '05/12/1986' union
select '874564561', 'Christine', '11/11/1985'

-- See the date
select * from @TempTable

--Update the EMPID to Null we will update one row at a time till all of them are done
update @TempTable set Empid = NULL

while (select count(*) from @TempTable where Empid is null or len(Empid) < 9 ) > 0
BEGIN
set rowcount 1
update @TempTable
set Empid =
-- --Random EMPID
(
select
substring(ch,convert(int,rand()*len(ch)-1),1)+
substring(ch,convert(int,rand()*len(ch)-2),1)+
substring(ch,convert(int,rand()*len(ch)-3),1)+
substring(ch,convert(int,rand()*len(ch)-4),1)+
substring(ch,convert(int,rand()*len(ch)-5),1)+
substring(ch,convert(int,rand()*len(ch)-6),1)+
substring(ch,convert(int,rand()*len(ch)-7),1)+
substring(ch,convert(int,rand()*len(ch)-8),1)+
substring(ch,convert(int,rand()*len(ch)-9),1)
from
(select ch =
replicate('0123456789',12)) a
)
-- --Change the Date
, birthdate = (
select
DateAdd(yy, -1 * cast(substring(ch,convert(int,rand()*len(ch)-1),1) as int), birthdate )
from
(select ch =
replicate('123456789',8)) a
)
-- -- Random Name (Not really a name but a string of characters)
, fname = (
select
substring(ch,convert(int,rand()*len(ch)-1),1)+
substring(ch,convert(int,rand()*len(ch)-2),1)+
substring(ch,convert(int,rand()*len(ch)-3),1)+
substring(ch,convert(int,rand()*len(ch)-4),1)+
substring(ch,convert(int,rand()*len(ch)-5),1)+
substring(ch,convert(int,rand()*len(ch)-6),1)+
substring(ch,convert(int,rand()*len(ch)-7),1)+
substring(ch,convert(int,rand()*len(ch)-8),1)
from
(select ch =
replicate('ABCDEFGHJKLMNPQURSUVWXYZ',8)+
replicate('abcdefghjkmnpqursuvwxyz',8)) a
)
where Empid is null
or len(Empid) < 9 -- Sometimes thge EMPID does not get 9 chars set rowcount 0 END select * from @TempTable

Leave a Reply

Your email address will not be published. Required fields are marked *