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
104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

751 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