Stop Losing Messages: How to Format Phone Numbers for WhatsApp API

A single misplaced zero can break your entire customer communication flow. If you run an e-commerce store in Karachi, a SaaS platform in Noida, or a logistics business in Lahore, your database is probably full of phone numbers written in ten different formats. Some have country codes, some start with a local trunk prefix like 0, and others are littered with spaces, dashes, and plus signs.

When you use a personal WhatsApp app, your phone's operating system does a lot of silent cleanup behind the scenes. The WhatsApp Business API does not. If you send a payload to the Meta Graph API with a poorly formatted number, the API will return an error, or worse, it will charge you for a failed delivery attempt.

This guide explains how to clean, format, and validate phone numbers for international WhatsApp messaging. We will look at the exact rules, the SQL and Excel formulas to fix your data, and how to prevent bad data from entering your system again.

What You Need Before Formatting Your Database

Before you run any update queries on your production database, you must understand the target format. WhatsApp requires phone numbers in the international E.164 format. This is a structured format regulated by the International Telecommunication Union, but you do not need to read their documentation to understand it.

For WhatsApp, the E.164 format requires three things:

• A country code (for example, 92 for Pakistan, 91 for India, 1 for the USA).
• A national destination code or mobile network prefix (without any leading zeros).
• The subscriber number.

Crucially, the final number must contain no spaces, no dashes, no parentheses, and no plus signs (+). It must contain only numbers.

If you feed +92-300-1234567 to the Cloud API, it will fail. You need a clean string of digits. Let us look at how to clean your existing database to achieve this.

The Step-by-Step Formatting Process

Cleaning a database of 10,000 or 100,000 numbers requires a systematic approach. Do not try to write a single complex regex to solve this in one go. You will mess up your data. Follow these four steps in order.

Step 1: Strip All Non-Numeric Characters

Your first task is to remove everything that is not a digit. This includes spaces, hyphens, brackets, and the plus sign.

If you are working in Microsoft Excel or Google Sheets, you can use a regex formula to clean a number in cell A2:

=REGEXREPLACE(A2, "[^0-9]", "")

If you are using PostgreSQL, you can run a query using regexp_replace to clean your column:

UPDATE users SET phone = REGEXP_REPLACE(phone, '[^0-9]', '', 'g');

This converts +92 (300) 123-4567 into 923001234567. It also converts 0300-1234567 into 03001234567.

Step 2: Remove the Leading Trunk Prefix

In Pakistan, India, and many other countries, domestic calls require a leading 0 (or sometimes 00). This is the trunk prefix. It must be removed before you prepend the country code.

If your cleaned number starts with a single zero, you must strip it. For example, 03001234567 must become 3001234567.

In SQL, you can target these numbers specifically:

UPDATE users SET phone = SUBSTRING(phone FROM 2) WHERE phone LIKE '0%';

Be careful here. Do not run a blind script that removes the first digit if it is a zero without checking the length first. A real number might legitimately start with a different digit if the country code is already present. Only strip the leading zero if the remaining string matches the expected length of a local mobile number (which is 10 digits in both India and Pakistan).

Step 3: Handle Double Zeros

Users sometimes type 0092 or 0091 instead of using a plus sign. After your Step 1 cleanup, these numbers will start with 00.

You must detect these double zeros and strip both of them. 00923001234567 must become 923001234567.

In SQL, you can handle this with:

UPDATE users SET phone = SUBSTRING(phone FROM 3) WHERE phone LIKE '00%';

Step 4: Prepend the Country Code

Once you have stripped non-numeric characters, removed the leading single zero, and removed any leading double zeros, you are left with a local number. For Pakistan, this will be a 10-digit number starting with 3 (e.g., 3001234567). For India, it will be a 10-digit number starting with 6, 7, 8, or 9.

Now you must add the country code. If you know all your customers are in Pakistan, you prepend 92. If they are in India, you prepend 91.

In SQL:

UPDATE users SET phone = '92' || phone WHERE LENGTH(phone) = 10 AND phone LIKE '3%';

This query ensures you only prepend 92 to 10-digit numbers that start with 3, preventing you from accidentally doubling the country code on numbers that were already formatted correctly.

How to Confirm Your Formatting Worked

Once you have processed your database, you must verify the results before sending messages. Sending messages to invalid numbers wastes API processing time and can trigger Meta's rate limits if your failure rate is too high.

The Low-Tech Test: Browser Verification

If you want to test a few manual entries to see if your formatting logic is correct, you can use a public link generator. For instance, WA Link allows you to create a quick clickable link to start a chat. You can use their tool to see if a number resolves, or you can manually type the URL in your browser:

https://wa.me/923001234567

If the page opens and shows a "Chat on WhatsApp with..." button with the correctly formatted number, your formatting is correct. If it shows an invalid number error, your E.164 structure is broken. Note that while WA Link is helpful for quick manual checks of single numbers, it cannot help you validate or clean a database of thousands of records.

The High-Tech Test: The Contact Verification API

For developers, the correct way to verify numbers at scale is using the WhatsApp Business API's contacts endpoint. Before you send a template message, you can send a POST request to verify if a number is registered on WhatsApp.

Here is what the API payload looks like when sent to https://graph.facebook.com/v20.0/YOUR_PHONE_NUMBER_ID/contacts:

{
  "blocking": "wait",
  "contacts": [
    "+923001234567",
    "923009999999"
  ],
  "force_check": false
}

The API will return a response showing which numbers are valid WhatsApp accounts:

{
  "contacts": [
    {
      "input": "+923001234567",
      "status": "valid",
      "wa_id": "923001234567"
    },
    {
      "input": "923009999999",
      "status": "invalid"
    }
  ]
}

Notice how the API accepted the input with the plus sign but returned the normalized wa_id without it. You should save this returned wa_id as your primary contact key. It is the absolute source of truth.

When Things Go Wrong: Common Errors and Fixes

Even with clean code, you will encounter edge cases. Here is how to handle the most common failures when dealing with international numbers.

Error 100 or Error 2018001: Invalid Parameter / User Not on WhatsApp

If you attempt to send a message to a number that does not exist on WhatsApp, the API returns an error. Usually, this is because the number is a landline or a fake number entered during signup.

The Fix: Do not keep retrying failed numbers. Implement a blocklist in your database. If a number returns a "user not on WhatsApp" error, flag it in your database as is_whatsapp_active = false. This prevents your system from attempting to message this number in future campaigns, protecting your quality rating with Meta.

The Mixed-Country Code Trap

If your business operates in both India and Pakistan, a blind script will break your data. If you have a local number 9876543210, is it an Indian number or a Pakistani number? Without a country selector on your signup form, you cannot know for sure.

The Fix: Never guess. If you do not know the country of origin, check the IP address of the user at the time of signup, or look at the prefix. Pakistan mobile numbers almost always start with 3 (after the trunk prefix). Indian mobile numbers start with 6, 7, 8, or 9. Use these patterns to build conditional formatting rules in your backend logic.

The "Silent Failure" of Landlines

Many businesses in Pakistan use fixed-line numbers (like 021-31234567 for Karachi). If you format this to 922131234567, the formatting is technically correct E.164, but the number will fail because landlines cannot receive WhatsApp messages unless they have been specifically registered as Business API numbers.

The Fix: Filter out landline prefixes before sending. In Pakistan, avoid sending automated API messages to numbers starting with 9221, 9242, 9251, etc., unless you have manually verified they are WhatsApp Business accounts.

What to Do Next to Keep Your Data Clean

Cleaning your database once is not enough. If you do not change how you collect data, your database will be dirty again within a week. You must enforce clean formatting at the point of entry.

First, update your signup forms. Do not use a single free-text field for phone numbers. Instead, use an international country code selector dropdown. This forces the user to select their country, which automatically provides the correct country prefix (e.g., +92).

Second, use frontend validation. If a user selects Pakistan, use JavaScript to verify that the remaining number they type is exactly 10 digits long and starts with a 3. If they type a leading zero, strip it automatically in the background before submitting the form.

Third, run a daily or weekly database cron job. This job should look for any newly created user records that do not conform to the E.164 digit-only format and clean them using the SQL rules outlined above. This keeps your database ready for marketing broadcasts or automated transactional alerts at any time.

Frequently Asked Questions

Do I need the plus sign (+) when sending messages via the WhatsApp API?

No. While the Meta API can sometimes parse numbers with a leading plus sign, the safest and most reliable format for the API is digits only (e.g., 923001234567). Removing the plus sign prevents parsing errors across different API versions and SDKs.

What happens if I send a message to a number that has a leading zero?

The message will fail. The WhatsApp API does not recognize numbers with local trunk prefixes like 9203001234567. You must strip the zero after the country code so it reads 923001234567.

Can I use the WhatsApp Business API to send messages to landline numbers?

You can only send messages to landline numbers if the owner of that landline has registered it as a WhatsApp Business account using voice verification. You cannot send messages to standard, unregistered landline numbers; these attempts will return an error.

How do I verify if a formatted number is actually on WhatsApp?

You should use the Meta Graph API's contacts endpoint. By sending a POST request with the phone number, the API will return a status of either "valid" or "invalid". This allows you to filter out non-WhatsApp numbers before starting your messaging campaigns.