Solved

How can I mask so many characters of a first name and last name when they are in the same column?

Posted on 2014-10-31
4
97 Views
Last Modified: 2015-04-22
If I have a column that provides a list of names in First Last format,  how can I mask it so that a few of the characters of the first name and so many characters of the last name are masked?  The first and last names are in the same column.

So if I have a name of Bob Jackson, I would like to see something like B** J******?
0
Comment
Question by:Erik Hauser
  • 3
4 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 125 total points
ID: 40416136
For oracle:

If the first and last name have normal capitalization (i.e. the first letter of each is capital, the rest lower) then try this..

SELECT REGEXP_REPLACE('Bob Jackson', '[a-z]', '*') FROM DUAL;

if that's not the case, but it's legal to treat them that way, try this...

SELECT REGEXP_REPLACE(INITCAP('bob JACKSON'), '[a-z]', '*') FROM DUAL
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 375 total points
ID: 40417294
For sql server, try (not tested) -
with cte as
(select charindex(' ',name) as space, len(name) - charindex(' ',name) as lengthlast from table)
select substring(name,1,1) + replicate('*',space - 2) + substring(name,space + 1,1) + replicate('*',lengthlast - 1) as masked
from cte;
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 375 total points
ID: 40417295
I think I missed concatenating a space between the first and last names, here -
...space - 2) + ' ' + substring...
0
 
LVL 32

Accepted Solution

by:
awking00 earned 375 total points
ID: 40417300
The space could also be maintained using -
select substring(name,1,1) + replicate('*',space - 2) + substring(name,space,2) + replicate('*',lengthlast - 1)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 34
RESTORE MASTER DATABASE -- NOW 2 20
Parse this column 6 27
SQL - Curser to do an insert based on a select 2 13
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Viewers will learn how the fundamental information of how to create a table.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question