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