First-Pass PII data redaction with SQL Regex at the core

Why Regex Redaction Can Still Be Useful

Cloud NLP tools can identify names, addresses, emails and other personal information with much greater sophistication than regex.

But there are still cases where a simple SQL-based approach is useful: exploratory analysis, quick comment review, low-cost first-pass redaction and reducing exposure before manual analysis.

Starting Point: Redacting Digits

The simplest version replaces every digit with #.

regexp_replace(RP.why_give_score, '\d', '#') AS Comments_Redacted

A More Advanced Comment Redaction Query

The more advanced version extracts comments from the request body, URL-decodes them, then applies a broader regex replacement.

WITH s1 AS (
  SELECT
    Date,
    generated_at,
    coalesce(
      regexp_extract(detail.referrer, 'referrerUrl\=(.*)', 1),
      regexp_extract(detail.referrer, 'gov\.uk(.*)', 1)
    ) AS ref,
    detail.request_body AS ReqBody,
    regexp_extract(tags.path,'[a-zA-Z\-|_]+(\/thanks)?$') AS reportType
  FROM {{time_aware_table("contact_frontend_requestreceived", timerange = "2 days")}}
  WHERE detail.method = 'POST'
)
SELECT
  Date,
  generated_at,
  url_decode(ref) AS "Referrer",
  regexp_replace(
    url_decode(regexp_extract(ReqBody, '&feedback\-comments\=([^\&]+)', 1)),
    '\d+|(?:.)(?<!\.\s|\p{Sc})[[A-Z]{1}&&[^.]]\w*|\S+@\S+\.\S+|[[A-Z]{1}&&[^.]]\w*$',
    ' ##'
  ) AS "Deskpro Comment",
  reportType
FROM s1

Why URL Decode Before Redaction?

The query applies url_decode() before regexp_replace(). This matters because submitted form data is often URL encoded.

Breaking Down the Regex

\d+|(?:.)(?<!\.\s|\p{Sc})[[A-Z]{1}&&[^.]]\w*|\S+@\S+\.\S+|[[A-Z]{1}&&[^.]]\w*$

Names at the End of a Message

A single trailing capitalised word may be caught, but a full name at the end of a message is harder.

For example:

Thanks,
David Hoffmann

Depending on line breaks, spacing and the regex engine, the current pattern may not reliably redact both words as one full name.

A more specific trailing-name pattern can help:

(?:[A-Z][a-z]+(?:\s+[A-Z][a-z]+){1,3})\s*$

Suggested Additional Redaction Pass

A practical improvement is to add a second regexp_replace around the existing redaction to catch full names at the end.

regexp_replace(
  regexp_replace(
    url_decode(regexp_extract(ReqBody, '&feedback\-comments\=([^\&]+)', 1)),
    '\d+|(?:.)(?<!\.\s|\p{Sc})[[A-Z]{1}&&[^.]]\w*|\S+@\S+\.\S+|[[A-Z]{1}&&[^.]]\w*$',
    ' ##'
  ),
  '(?:[A-Z][a-z]+(?:\s+[A-Z][a-z]+){1,3})\s*$',
  ' ##'
) AS "Deskpro Comment"

What This Catches Well

• numbers
• reference IDs
• phone numbers
• email addresses
• many capitalised names
• some proper nouns

What It Misses

Regex redaction has clear limits. It may miss lowercase names, addresses, unusual spellings, names split by punctuation, and sensitive information expressed indirectly.

Conclusion

Regex redaction is not a replacement for specialist PII detection or cloud NLP tools. But as a first-pass method inside SQL, it can remove a useful amount of obvious sensitive data before the data reaches dashboards, exports or manual review.