Introduction

The first thing anyone that has worked with real world data will tell you is that rarely does data come in the clean, uniform format you expect. Phone numbers are one of the biggest culprits.

One person enters +254712345678, another writes 0712345678, someone else throws in 254-712-345-678, another (254)-712345678 and another 254 712 345 678. Before you know it, your database has a different version of phone numbers in every row.

In this article, we will explore how to fix that, using a powerful PostgreSQL function called regexp_replace.

By the end, you will understand what it does, how to build the pattern, and how to apply it to actually clean your data.

First, What Even Is regexp_replace?

First things first, we need to understand what this function is and what it does.

regexp_replace is a PostgreSQL function that finds a pattern inside a string and replaces whatever matches that pattern with something else you specify. The word regexp is short for regular expression.

The syntax looks like this:

regexp_replace(source, pattern, replacement_string, flag)

Let us break each part down simply:

Now that we have the hang of it, let us move on to building the pattern.

Understanding the Special Characters in Patterns

A pattern is basically a set of rules that tells PostgreSQL what to look for. It is like saying "find any character that is not a digit" or "find this pattern, but only at the beginning" etc.

To write these rules/instructions, there are special characters that are used.

Here are the ones we will use today:

The ^ character

Depending on where it is placed, this will do either of 2 things;

When ^ is at the beginning of a pattern it means: start matching from the very beginning of the string.

^'hello'

This means: only match hello if it appears at the start of the string.

^[0-9]

This means: Only the first character of a string is checked and matched if it is a digit. Anything after that is ignored.

When ^ is inside square brackets [ ], it means: NOT these characters.

[^0-9]

This means: Every character in the string is checked, any one that is not a digit gets matched.

Same symbol, two different purposes depending on its position.

The \ character (backslash)

The backslash is used to tell PostgreSQL: treat the next character as a character, not as a special operator.

For example, the + sign in regular expressions has a special meaning (we will cover it in a moment). So if you want to find a plus sign + in your data, like +254, you write it as \+ to tell PostgreSQL "I mean the actual plus sign, not the operator."

Square brackets [ ]

Square brackets let you define a group of characters to match. For example:

• [0-9] means match any digit from 0 to 9
• [a-z] means match any lowercase letter
• [^0-9] means match anything that is NOT a digit (the ^ inside means NOT, as we've mentioned above)

The + operator

When used outside brackets, + means: keep matching one or more of the preceding character or group.

So [0-9]+ means: Match the digits as long as they are all next to each other with no other characters in between. (Without the + , it stops after the first match)

The $ character

Just like ^ marks the start of a string, $ marks the end. When you write $ at the end of your pattern, you are saying: the string must end here, nothing else allowed after this.

Putting them together: ^[0-9]+$

Now that you know each piece, read this pattern again:

^[0-9]+$

• ^ → start from the beginning of the string
• [0-9] → the characters must be digits
• + → keep going, there can be one or more of them
• $ → and this must be the end of the string, nothing else after

All together: the entire string, from start to finish, must contain only digits.

This is the pattern we will use to identify which phone numbers are already clean and which ones need fixing.

Our Problem: What Does the Messy Data Look Like?

Let us say when we run this:

SELECT passenger_phone FROM dirty_safari_data;

We get back a mix of things like:

+254712345678
254712345678
0712345678
0712-345-678
+254 712 345 678
(0) 712345678

The goal is to standardize all of these into the local format starting with 0, containing only digits, like:

0712345678

Two problems to solve:

1. Some numbers start with +254 or 254 instead of 0
2. Some numbers have extra characters like brackets, spaces, dashes, or plus signs

Step 1: Replace +254 or 254 at the Start With 0

The first thing we want to do is find any phone number that starts with +254 or 254 and swap that prefix out for 0.

SELECT regexp_replace(passenger_phone, '^(\+254|254)', '0', 'g')
FROM contacts;

Let us read the pattern ^(\+254|254) carefully:

• ^ → look at the start of the string only
• ( ) → group what is inside together
• \+254 → literally the characters +254 (the backslash makes + a literal character)
• | → OR
• 254 → literally the characters 254

So the full pattern says: at the start of the string, find either +254 or 254.

The replacement is '0', so wherever that prefix is found, replace it with 0.

The flag is 'g' for global.

After this step, +254712345678 becomes 0712345678 and 254712345678 also becomes 0712345678.

Step 2: Remove Any Character That Is Not a Digit

Some numbers still have spaces, dashes, or other characters sitting in them. The second regexp_replace cleans all of that up.

SELECT regexp_replace(passenger_phone, '[^0-9]', '', 'g')
FROM contacts;

The pattern [^0-9] means: any character that is NOT a digit.

The replacement is '' (an empty string), so we are just deleting those characters.

The 'g' flag means do this for every non-digit character found in the string, not just the first one.

So 0712-345-678 becomes 0712345678 and 0712 345 678 also becomes 0712345678.

Step 3: The WHERE Clause — Only Update What Needs Fixing

We do not want to touch phone numbers that are already clean. That is where our ^[0-9]+$ pattern comes in handy. We use it in the WHERE clause to filter for records where the phone number is not already a clean string of digits.

You might be tempted to write it like this:

WHERE passenger_phone != '^[0-9]+$'

But this is not correct. The != operator just compares strings literally, so this would check if the phone number is literally equal to the text ^[0-9]+$, which is not what we want.

In PostgreSQL, to check whether a string does NOT match a regular expression, you use the !~ operator. The corrected version should be:

WHERE passenger_phone !~ '^[0-9]+$'

This now correctly says: only return (or update) rows where the phone number does not match the pattern of being purely digits from start to finish.

Putting It All Together

The SELECT query (to preview your cleaned data before changing anything)

Always preview before you update. This is a good habit.

SELECT 
    passenger_phone AS original,
    regexp_replace(
        regexp_replace(passenger_phone, '^(\+254|254)', '0', 'g'),
        '[^0-9]', '', 'g'
    ) AS cleaned_phone
FROM contacts
WHERE passenger_phone !~ '^[0-9]+$';

Notice how we nested one regexp_replace inside another. The inner one runs first (fixing the prefix), and then the outer one runs on the result (removing non-digits). It is like running two cleaning steps in one go.

The UPDATE query (to actually apply the changes)

Once you are happy with the preview, run the update:

UPDATE contacts
SET passenger_phone = regexp_replace(
    regexp_replace(passenger_phone, '^(\+254|254)', '0', 'g'),
    '[^0-9]', '', 'g'
)
WHERE passenger_phone !~ '^[0-9]+$';

This updates only the rows that have messy phone numbers, leaving the already clean ones untouched.

Quick Recap

Here is a summary of everything covered:

Final Thoughts

Regular expressions can look intimidating and confusing at first, but once you understand what each character means and why it is there, they start making a lot of sense. The key takeaway here is to build your pattern step by step, rather than trying to write the whole thing at once.

Also, always test with a SELECT before running an UPDATE. You can never be too careful when modifying data directly in a table.