The Hidden Characters That Broke Our KPIs: A Deep Dive into Text Cleaning with PostgreSQL REGEX
Hi everyone,
Welcome back to my blog!
Data analysis is not just about running analytical tasks. It starts with by collecting data from different APIs, combining it based on the requirements, and staging it in the right place. Then we filter what is useful and remove what isn’t, while making sure the data stays accurate and consistent.
After that comes the basic cleaning: fixing mismatched values, correcting inconsistencies, handling outliers, and resolving any discrepancies. Only then do we move into Exploratory Data Analysis (EDA), followed by descriptive, prescriptive, and predictive steps to uncover patterns and insights.
So mostly 50 to 60 percent of the time would be spent on Data Scrapping and Data Cleaning process. Of course, we will be sure of the major big issues that will spark in our mind always but the smallest, most invisible issues create the biggest analytical distortions. One such challenge surfaced during our maternal health project, where we were analyzing patient cohort data to understand how maternal factors influence delivery outcomes.
Among the many variables in our dataset, some columns contain free‑text responses. For example, patients in the cohort were asked to write the reasons for their medical conditions, the type of delivery they had in their past and recent history, and the reason for choosing that delivery method.
These columns do not have standardized values; instead, they contain long and short text entries with varied wording and occasional typos. Cleaning this type of data is especially challenging in healthcare because we cannot leave the text as it is, nor can we ignore it completely.
Our Initial Approach:
As this was my first time working with real‑time data, we followed the guidelines we had learned so far. We made sure we clearly understood the requirements, and we also did subject‑matter research to familiarize ourselves with medical terms, clinical ranges, and best practices.
We began by staging the data. Then we standardized the column names, assigned appropriate data types, and performed deduplication. Since this was patient data, we had to ensure there were no multiple entries and that unique key constraints were maintained. We also standardized numerical columns and trimmed extra spaces in text columns.
Everything seemed fine, so we moved on to building the EDA dashboard. We started calculating different KPIs and exploring the nature and distribution of the data. However, we later noticed differences between my results and my colleague’s results. To cross‑check, we manually reviewed the Excel file and discovered that one of the text columns had a row with invisible leading spaces that didn’t appear as empty rows. In Excel, we realized that the row contained a value, but it had nearly two and a half lines worth of leading spaces. We had missed removing those spaces earlier.
Reason For the Problem:
Image source: educba.com
I have generically used TRIM Function alone to clear the white spaces, that is the hidden reason behind it. Because TRIM Function only removes the normal leading or trailing white spaces alone, they cannot identify the following spaces:
\u00A0 (non‑breaking space)
\u2002, \u2003 (en‑space, em‑space)
\u200B (zero‑width space)
\t (tab)
\n (newline)
\r (carriage return)
We usually know the tab, newline and carriage return spaces. I can give you an example for the other spaces for your better understanding.
Nonbreaking Space:
Nonbreaking space is nothing but “Hello·World”. Here “.” Shows the normal space like the regular space we type using the spacebar. Uni code is \u00A0.
En Space:
En space is nothing but the space width between the words roughly the same as the letter N in the current font. Unicode is \u2002. Eg: Hello World
Em Space:
Em Space is wider than En Space and Named after the letter M. Its width equals the width of the letter M in the font. Unicode is \u2003. Eg: Hello World
Usually, Trim Function will not identify above mentioned spaces. That is the reason for the issue.
Solution for the Problem:
I resolved the issue by using PostgreSQL’s powerful REGEX function, which helped clean the invisible spaces that TRIM couldn’t handle.
The syntax that I used was:
The common syntax for REGEX is:
regexp_replace(trim(column_name), '^\s+|\s+$', '', 'g')
trim(column_name) → removes normal spaces first
^\s+ → remove any whitespace at the start
\s+$ → remove any whitespace at the end
'g' → global replace
Regular Expression or REGEX:
REGEX is a strong and powerful function in PostgreSQL to search or identify patterns, remove white spaces and compare two characters effectively.
PostgreSQL supports POSIX regular expressions, a standardized set of regular expressions defined by POSIX (Portable Operating System Interface). The Syntax is not Universal. Because not all SQL Engines support the same.
Based on my experience, TRIM and REPLACE alone are not effective for removing invisible spaces. The reliable solution is to use REGEX together with these functions. Such understanding develops only when we work with real‑time data. If we simply study these concepts elsewhere, we may know they exist, but we won’t truly understand where and when to use them.
Now, let’s go a little deeper into this topic and understand POSIX REGEX, its limitations, and how it differs from the other pattern‑matching functions we commonly use in PostgreSQL. I’m exploring this because I want to clearly understand the difference between REGEX pattern matching and the Basic Pattern Matching, which we normally use.
Basic Pattern Matching Vs REGEX:
The Basic Pattern Matching works with LIKE or ILIKE (when it is case sensitive) keywords along with only two wildcard characters (“_” for Single character and “%” for any number of characters).
Whereas REGEX is kind of all-in-one stop solution. It is more powerful than the latter one.
You can match patterns like:
only digits → ^[0-9]+$
only letters → ^[A-Za-z]+$
whitespace → \s
invisible spaces → \s+
start of string → ^
end of string → $
optional characters → ?
repeated characters → +, *
Conclusion:
Invisible spaces may look harmless, but they can silently distort KPIs, break grouping logic, and create inconsistencies across dashboards. This experience taught me that real‑world data cleaning goes far beyond basic TRIM and REPLACE functions. Understanding POSIX REGEX and knowing when to use it can make the difference between misleading insights and accurate analytics. As analysts, our job is not just to analyze data, but to truly understand it, clean it, and prepare it with precision.
Dive in and enjoy the insights!
Happy Reading!
References:
POSIX Regular Expressions — pgtutorial.com
