How to Mask individual Field in a Table

Transact-SQL
1

Dears,
I am new to SQL and I have one requirement where I would like to mask 'Date of Birth' field (YYYYMMdd) in which I have to mask date (dd) field to any random value (i.e. with valid Date from 0-31).

I have created one SQL script which randomly picks value from one temp table (#DateDomain) where I have stored the dates from 01 till 31.

create table #DateDomain (
id int IDENTITY(1,1) PRIMARY KEY,
domain varchar(500) not null
)
--id is automatic generated
insert into #DateDomain values('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09'), ('10'), ('11'), ('12'), ('13'), ('14'), ('15'), ('16'), ('17'), ('18'), ('19'), ('20'), ('21'), ('22'), ('23'), ('24'), ('25'), ('26'), ('27'), ('28'), ('29'), ('30'), ('31')

I have one table [dbo].[myTestSkipCol] consists of 3 records, date of birth is present under 'DBTH' column
|Col1|Col2|Col3|DBTH|
|1|DEFAULT_VALUE|04100895897|19890831|
|2|DEFAULT_VALUE|04100938866|19911120|
|3|DEFAULT_VALUE|04100523110|19960201|

Below is my masking SQL query -
declare @randomDOB as varchar(500)
declare @countOfDOB as int = (select COUNT(*) from [dbo].[myTestSkipCol])
declare @incrementCount as varchar(500)
declare @decrementDOBCount as varchar(500)

set @incrementCount = 0
set @decrementDOBCount = @countOfDOB

while @incrementCount < @countOfDOB
BEGIN
set @randomDOB = (SELECT TOP 1 domain FROM #DateDomain
ORDER BY NEWID())
-- print( @randomDOB )

while @decrementDOBCount > 0
BEGIN
;with cte as (
select DBTH from [dbo].[myTestSkipCol] DOB
)
select DBTH, left(DBTH, abs(len(DBTH + ',') - 3))+ @randomDOB AS MASKED_DBTH
from cte
set @decrementDOBCount -= 1
break
END;
set @incrementCount += 1
END;

Output of the current SQL Query
19890831 19890822
19911120 19911122
19960201 19960222

19890831 19890816
19911120 19911116
19960201 19960216

19890831 19890802
19911120 19911102
19960201 19960202

Here, the problem with my query is that it not assigning a random value to each record.
Ideally, the output should come as (mentioned below) where each record will have unique random date -
19890831 19890822
19911120 19911116
19960201 19960202

But the query is not working as expected.

Could you please assist me on this? Any help would be appreciated.

Thank you in advance.

2

please let me know if this helps ...
:slight_smile:

3

You should be able to adapt this:

if OBJECT_ID('tempDb..#temp') is not null drop table #temp;

create table #temp
(
id varchar(20),
DHTH char(10)
)

insert #temp values ('04100895897','19890831' );
insert #temp values ('04100938866','19911120' );
insert #temp values ('04100523110','19960201' );

go

select top 1 with ties 
a.id 
,a.DHTH 
,b.days
,convert(char,dateadd(DAY,b.days ,DATEADD(DAY , - day(a.DHTH),a.DHTH) ),112) [MASKED_DBTH]

from #temp a
cross apply 
	(
	select top (day(dateadd(mm,DateDiff(mm, -1, a.DHTH) ,0) -1))  ROW_NUMBER() over(order by object_id asc) [days]
	from sys.objects
	) b

where b.days <> day(a.DHTH) 

order by ROW_NUMBER() over(partition by a.id, a.DHTH order by newid())

drop table #temp;