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
85 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 73

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 31

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 31

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 31

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now