October 28, 2014 at 1:10 pm
Hi,
I need to create view to mask the sensitive date.
I do have 10 columns in a table. I need to create view on that table and mask some columns id, customer firstname, lastname, address, contact no, zipcode.
I have two kinds of customers in that table based on that id joining with some other table (CustomerInformation), I have to show zipcode first 3 letters or first 4 digits.
If Id exists on this Information table it should display first 3 digits or else it should display 4 digits like that. This where condition has to apply only for zipcode column.
How can I create view in this situation?
October 28, 2014 at 1:40 pm
It makes it very difficult to help without schema info, sample data, and expected results. read the link in my signature and post more info if the following does not help get you started.
you could just use a case statement to check for the existence of id and then substring the zip.
CREATE TABLE #tmp(id int,
zip varchar(8),
name varchar(10));
INSERT INTO INTO #tmp
VALUES(1,
'123456',
'one'),
(NULL,
'654321',
'two');
SELECT name,
CASE
WHEN id IS NOT NULL THEN SUBSTRING(zip, 1, 3)
WHEN id IS NULL THEN SUBSTRING(zip, 1, 4)
END AS zip
FROM #tmp;
October 28, 2014 at 1:55 pm
Here's an option on how to do it.
CREATE TABLE Customer(
custid int,
firstname varchar(50),
lastname varchar(50),
zipcode varchar(10))
INSERT INTO Customer VALUES
(1, 'Luis', 'Cazares', '12345'),
(2, 'Mike', 'Peterson', '453125'),
(3, 'Phil', 'Coulson', '686462'),
(4, 'Melinda', 'May', '5475469')
CREATE TABLE CustomerInformation(
custid int)
INSERT INTO CustomerInformation VALUES(1), (3)
SELECT c.custid,
c.firstname,
c.lastname,
LEFT( c.zipcode, ISNULL(ci.digits, 4)) zipcode
FROM Customer c
LEFT
JOIN (SELECT custid, 3 digits FROM CustomerInformation) ci ON c.custid = ci.custid
SELECT c.custid,
c.firstname,
c.lastname,
LEFT( c.zipcode, CASE WHEN ci.custid IS NULL THEN 4 ELSE 3 END) zipcode
FROM Customer c
LEFT
JOIN CustomerInformation ci ON c.custid = ci.custid
GO
DROP TABLE Customer
DROP TABLE CustomerInformation
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply