ETL: NormalizePhone

Normalize a phone number string to E.164 format in Integrate.io ETL expressions for consistent storage, deduplication, and downstream matching.

Description

Normalizes a phone number string to E.164 format (a leading +, country code, and subscriber number with no spaces or punctuation). Use it to standardize phone values from mixed sources before storing, deduplicating, or joining on phone.

Inputs without a + country-code prefix are interpreted in the region given by the optional region argument (default US). Extensions such as x123 or ext. 5 are parsed and dropped, since E.164 does not include them.

Syntax

NormalizePhone(string_expression[, region[, on_invalid]])

Arguments

Argument	Description
string_expression	Required. The phone value to normalize.
region	Optional. ISO 3166 region code used to interpret numbers without a + prefix. Case-insensitive. Defaults to US. An unsupported region returns null. A blank value falls back to the default.
on_invalid	Optional. Controls the return value when the input cannot be parsed as a valid phone number. Case-insensitive. Defaults to null.

on_invalid modes

Mode	Behavior on invalid input
null (default)	Returns null.
passthrough	Returns the original input, trimmed and unchanged.
e164_digits	Best-effort fallback. Strips the input to digits and forces an E.164-shaped string. If the input starts with +, returns + followed by those digits. Otherwise prefixes the region’s country code. Requires at least 10 digits, or returns null. Does not validate the result.

The on_invalid fallback only triggers when normalization fails. A value that is a valid phone number always returns validated E.164 regardless of this setting.

Examples

NormalizePhone('[phone]') returns '[phone]'.

NormalizePhone('415-555-2671') returns '[phone]'.

NormalizePhone('020 7946 0958', 'GB') returns '[phone]'.

NormalizePhone('not a number', 'US', 'passthrough') returns 'not a number'.

NormalizePhone('32145678901234', 'US', 'e164_digits') returns '[phone]'.

NormalizePhone('123', 'US', 'e164_digits') returns null (fewer than 10 digits).

Return value datatype

String

Impact of null value

Returns null for any of the following:

• Null, empty, or whitespace-only input
• A region argument that is not a supported ISO 3166 region
• Input that cannot be normalized when on_invalid is null (or omitted, or an unrecognized value)
• on_invalid is e164_digits but the input has fewer than 10 digits