CODEWITHSUNDEEP
postgresql
shionblackcat
shionblackcat

Reputation: 21

How to mask email in PostgreSQL

How do I mask the email address string in PostgreSQL?

I want to mask email as follows [email protected] into te*****[email protected]

I have tried to address using

However PostgreSQL doesn't have a neat solution similar to excel =REPLACE (old_text, start_num, num_chars, new_text) where you get to set the start_num and num_chars

Upvotes: 2

Views: 3517

Answers (2)

Eugene Khyst
Eugene Khyst

Reputation: 10315

To mask emails in PostgreSQL you can use regexp_replace function.

It has the syntax regexp_replace(source, pattern, replacement [, start [, N ]] [, flags ]).

SQL query:

SELECT t.email, 
       regexp_replace(t.email, '(.{2}(.+)(.{2})(@.+))', '$1*****$3$4') AS masked_email 
  FROM tbl t;

Output:

| email               | masked_email        |
|---------------------|---------------------|
| [email protected] | te*****[email protected] |

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247235

Try this:

SELECT overlay(
          '[email protected]'
           placing repeat('*',
                          position('@' in '[email protected]') - 5
                         )
           from 3
           for position('@' in '[email protected]') - 5
       );

       overlay       
---------------------
 te*****[email protected]
(1 row)

You can create an SQL function out of that for comfort:

CREATE FUNCTION mask_email(text) RETURNS text
   LANGUAGE SQL IMMUTABLE AS
$$SELECT overlay($1 placing repeat('*', position('@' in $1) - 5) from 3 for position('@' in $1) - 5)$$;

Upvotes: 3

Related Questions