1

Due to privacy concerns, I need to hide personal information, while remaining somewhat understandable. I propose to achieve that by hiding everything except the first two characters. The word length should stay the same:

FirstName SecondName LastName -> Fi****** Se******** La******

My attempt is working, but not 100%:

SELECT regexp_replace('Firstname Secondname Lastname', '\y(\S)(\S)','\1' 
       || repeat('*', length('\2')),'g');

Output:

F**rtname S**condname L**stname

With only first character it is easier, and it works, but I need the second character too:

SELECT regexp_replace('Firtname Secondname Lastname', '\Y\w', '*','g'); 

Output:

F******* S********* L*******

Demo

0

2 Answers 2

2

You can use this regex:

SELECT regexp_replace('Firtname Secondname Lastname', '(?<=\w{2})[^\s]', 
       '*', 'g');

(?<=) is a "positive lookbehind"

(?<=\w{2})[^\s] match all non-space characters IF they are preceded by the two first character of a word boundary.

[^\s] may also be replaced by \S. I believe \s is more widely known rather than \S.

0
0

This question is an example of the XY problem!

The XY problem is (from the link):

where the question is about an end user's attempted solution (Y) rather than the root problem itself (X)

The OP asks for a regex to do a job when in fact there is a more efficient method which doesn't use regexes which are notoriously expensive.

The link above even gives an example of an XY problem as

  • Asking how to construct a regular expression [emphasis mine] to extract values from XML (Y) instead of how to parse XML (X).

which is not too distant from how the OP has phrased their question.

I took the following approach using the SPLIT_PART function (all of the code below is available on the fiddle here):

CREATE TABLE nom (name TEXT NOT NULL);

and populate:

INSERT INTO nom VALUES ('Firstname Secondname Lastname');

and then:

SELECT
  LEFT(SPLIT_PART(n.name, ' ', 1), 2) AS fname,
  LEFT(SPLIT_PART(n.name, ' ', 2), 2) AS mname,
  LEFT(SPLIT_PART(n.name, ' ', 3), 2) AS lname
FROM nom n;

Result:

fname mname lname
Fi Se La

And then:

SELECT
  LEFT(SPLIT_PART(n.name, ' ', 1), 2) || REPEAT('*', LENGTH(SPLIT_PART(n.name, ' ', 1)) - 2) || ' ' ||
  LEFT(SPLIT_PART(n.name, ' ', 2), 2) || REPEAT('*', LENGTH(SPLIT_PART(n.name, ' ', 2)) - 2) || ' ' ||
  LEFT(SPLIT_PART(n.name, ' ', 3), 2) || REPEAT('*', LENGTH(SPLIT_PART(n.name, ' ', 3)) - 2) AS code_name
FROM nom n;

Result:

code_name
Fi******* Se******** La******

which is the desired result. The REPEAT function (minus 2) serves to ensure the requirement that the "Word length should stay same".

Now, this becomes interesting when we look at performance. I used a RANDOM_STRING function (from here and shown at the bottom of the answer) and GENERATE_SERIES to generate 200,000 random 1st, 2nd and 3rd names as follows:

INSERT INTO nom
SELECT
  RANDOM_STRING(7) || ' ' || RANDOM_STRING(8) || ' ' || RANDOM_STRING(9)
FROM GENERATE_SERIES(1, 200000);

Then I ran:

EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING)
SELECT
  LEFT(SPLIT_PART(n.name, ' ', 1), 2) || REPEAT('*', LENGTH(SPLIT_PART(n.name, ' ', 1)) - 2),
  LEFT(SPLIT_PART(n.name, ' ', 2), 2) || REPEAT('*', LENGTH(SPLIT_PART(n.name, ' ', 2)) - 2),
  LEFT(SPLIT_PART(n.name, ' ', 3), 2) || REPEAT('*', LENGTH(SPLIT_PART(n.name, ' ', 3)) - 2)
FROM nom n;

Result:

QUERY PLAN
Seq Scan on nom n  (cost=0.00..13974.50 rows=200056 width=96) (actual time=0.049..235.528 rows=200001 loops=1)
  Buffers: shared hit=929 read=542 dirtied=580 written=510
Planning Time: 0.084 ms
Execution Time: 244.938 ms

Ran it 3 times - 244, 241, 242 ms - average 242ms - a quarter of a second!

I did the same with the regex solution accepted answer. The fiddle for this performance analysis is available here.

EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING)
SELECT regexp_replace(name, '(?<=\w{2})[^\s]', '*', 'g')
FROM nom;

Result:

QUERY PLAN
Seq Scan on nom  (cost=0.00..3971.70 rows=200056 width=32) (actual time=0.223..6495.448 rows=200001 loops=1)
  Buffers: shared hit=946 read=525 dirtied=526 written=493
Planning Time: 0.048 ms
Execution Time: 6542. ms

6,542.844 ms, 6,504.226 ms 6,542.163 - average 6,530ms - over 6.5 seconds!

It is my understanding that the db<>fiddle architecture is one of microcontainers where databases are created on the fly, so the results of one trial cannot influence the results of any other.

Now, (242/6530) * 100 = 3.7, so the SPLIT_PART solution takes 3.7% of the time of the regex one (~ 27 times faster).

Solutions that use regular expressions should be the the "last resort" and other string functions should be used if possible!

Rather than doing this many times, you can do it automatically by using a GENERATED field (manual) as follows (see fiddle here):

CREATE TABLE gf 
(
  istr TEXT NOT NULL, 
  gstr TEXT GENERATED ALWAYS AS  
    (LEFT(SPLIT_PART(istr, ' ', 1), 2) || 
     REPEAT('*', LENGTH(SPLIT_PART(istr, ' ', 1)) - 2)) STORED
);

Now we insert a record:

 INSERT INTO gf VALUES ('RANDOM_STRING');

and then we run SELECT * FROM gf:

Result:

istr gstr
RANDOM_STRING RA***********

So, we don't have do the calculation on the fly. There is a space penalty to pay, but that's never been an issue for me in the last 20 years in IT - disk space, even SSD, is cheap these days. Some systems offer a VIRTUAL generated field where it is calculated automatically on the fly, but PostgreSQL doesn't support this.

RANDOM_STRING function

Create or replace function random_string(length integer) returns text as
$$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;
1
  • Thank you for high quality answer. Intresting and more robust approach with SQL. BTW I did not ask for regexp. It was just my approach. At the same time, I did not make it clear that other ideas were welcome.
    – oren
    Aug 23, 2023 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.