CODEWITHSUNDEEP
sqlsql-server
Terry
Terry

Reputation: 1

SQL masking on reports

We are using SQL Server 2008 SR1, Report Builder 2.0 I have searched all sorts of sites regarding masking but struggle to make it work correctly. I wish to mask all but the last 4 digits of the SSN I’ve tried several changes with =Right(Fields!ssn.value,4, “NameInfo”) But I get an error due to changing it from =First to Right Just not quite sure how to go about doing it correctly to achieve a mask

There is a Textbox5 with the expression:

=First(Fields!ssn.Value,"NameInfo")

One of my datasets is NameInfo and the query for the dataset:

SELECT nm.FullName2 
, nm.ssn
, nm.dob
, nm.SexDesc
FROM jw50_Name AS nm
WHERE NameID = @NameID

When I modify either the expression or try to include in the dataset query all I generate are errors. Obviously am new to SQL and hoping someone might have a suggestion to help mask the ssn on the report - thank you for time

Upvotes: 0

Views: 1072

Answers (1)

Rahul
Rahul

Reputation: 77896

I wish to mask all but the last 4 digits of the SSN

Considering that your SSN value is of 10 digit (for example), you can use string functions SUBSTRING() and REPLICATE() to get this done like

SELECT REPLICATE('X',6) + SUBSTRING(SSN_Field, 7,4) as SSN
FROM table1;

Upvotes: 2

Related Questions