Shape / columns · All operations

Standardise addresses, names and phone numbers

Parse and standardise addresses, names, phone numbers.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today, with the us_address_standardizer extension.

Shape of the result
one row out per row in, with columns added or changed
Level
needs a DuckDB extension

The DuckDB step

A US address parsed into its parts

SQL
INSTALL us_address_standardizer FROM community; LOAD us_address_standardizer;
CREATE TEMP TABLE result AS
SELECT parse_address('123 Main Street, Springfield, IL 62701') AS a;
SELECT a.* FROM result;
SELECT CASE WHEN ((SELECT a.num = '123' AND a.city = 'Springfield' AND a.state = 'IL' AND a.zip = '62701' AND a.street ILIKE 'main%' FROM result)) THEN 'as expected' ELSE error('the address did not come apart into number, street, city, state and zip') END AS verdict;
  • The statement builds its own data.
  • It needs the us_address_standardizer extension.
  • The statement checks its own result and raises an error when it is wrong: “the address did not come apart into number, street, city, state and zip”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (2)
  • CASSread 2026-09-27

    Check and correct US addresses against the USPS CASS standard

  • Parse Addressread 2026-09-27

    Break a street address into number, street name, suffix and other parts

Talend (10)
  • tAddressRowCloudread 2026-09-27

    Verify, correct and format international addresses against online reference data from several providers

  • tBatchAddressRowCloudread 2026-09-27

    Parse and correct addresses in batches against online reference data from Loqate or MelissaData

  • tLoqateAddressRowread 2026-09-27

    Parse, verify, correct, transliterate and format international addresses with Loqate

  • tMelissaDataAddressread 2026-09-27

    Validate, correct and standardise the address in each row with Melissa Data

  • tPersonatorread 2026-09-27

    Check, verify, move and append US and Canadian contact data

  • tQASAddressIncompleteread 2026-09-27

    Check addresses against QAS QuickAddress data and output the incomplete ones and rejects

  • tQASAddressRowread 2026-09-27

    Correct formatting and spelling of addresses with QAS and give a verification status per row

  • tQASAddressUnknownread 2026-09-27

    Check addresses against QAS QuickAddress data and output those with no deliverable match

  • tQASAddressVerifiedread 2026-09-27

    Check addresses against QAS QuickAddress data into verified, interaction required and reject flows

  • tQASBatchAddressRowread 2026-09-27

    Correct addresses, add missing data and give a verification status per row from QAS batch files

Background

Standardising data brings every row of a column to one format or convention, such as expanding the abbreviations st and rd to street and road.[1] Standardising a US postal address converts it into a standard format, correcting it where possible and adding missing parts such as a ZIP code.[2] E.164, an international standard, defines a general format for international telephone numbers, limited to digits and to a maximum of fifteen digits.[3]

When to use it

In a plain string comparison, 30 W 26th St Fl #7 and 30 West Twenty-sixth Street Floor Number 7 differ, although a person can see that both name the same place.[4] libpostal, a C library for parsing and normalising street addresses around the world, aims to produce normalised strings parsed into components so that software can judge how well two addresses match and decide which are duplicates.[4] In Splink, a record linkage library, the features in a dataset are the evidence for whether two records match, and the quality of a model is dictated by the features provided.[5] Reach for standardisation before you deduplicate, join or link records on an address, a name or a phone number, because those comparisons are only as good as the values they compare. (Meridian's view)

Standardising an address is not the same as verifying it: postal address verification checks that an address is valid and deliverable, and in the United States an address must first be standardised before it can be certified as deliverable.[2] libpostal is not itself a full geocoder, though it can be used as a preprocessing step for one.[4] Treat a standardised address as a cleaner spelling of what was written, not as proof that the place exists. (Meridian's view)

In North America it is a common error to drop the plus sign in front of a number and write it like 1-555-123-4567, which is technically incorrect.[6] Keep the raw phone column beside the standardised one, so a number the parser rejects or misreads can still be recovered. (Meridian's view)

Spanish-speaking people commonly have two family names.[7] Two different data entry operators given the same person's name will not necessarily enter identical strings, even on a single system.[8] Standardise names for matching, in a derived column, and leave the name a person gave you as they gave it. (Meridian's view)

The method

PostGIS's address standardizer is a single line address parser that normalises an input address based on a set of rules stored in a table and helper lex and gaz tables.[9] The PostGIS address standardizer's parser works from right to left, looking first for the postcode, state or province and city, and then deciding whether it has a house number and street, an intersection or a landmark.[9] In the PostGIS address standardizer, the lex table classifies alphanumeric input and links it to input tokens and standardised representations, and the gaz table does the same for place names.[9] The PostGIS address standardizer does not currently look for a country code or name, and assumes an address is in the US or Canada.[9] DuckDB's us_address_standardizer community extension ports the rule-based parse_address and standardize_address parsers from the PostGIS address_standardizer, and adds addrust_parse, a Rust-based parser that needs no reference tables.[10]

libpostal's international address parser uses Conditional Random Fields and is trained on over 1 billion addresses in every inhabited country on Earth, built from OpenStreetMap and OpenAddresses.[4] libpostal's expand_address converts messy real-world addresses into normalised equivalents suitable for search indexing and hashing, turning Quatre vingt douze R. de l'Église into 92 rue de l eglise.[4] libpostal detects the language of an address with a classifier trained on OpenStreetMap so that it can apply the right normalisations, and supports them in 60+ languages.[4]

In the United States, standardising an address uses the abbreviations the United States Postal Service (USPS) approves, so that avenue, AV, AVEN and AVN all become AVE.[2] Each ZIP Code in the USPS Address Management System has one preferred city name and may have one or more acceptable city names, and New York City and Manhattan in ZIP Code 10001 are standardised to New York.[2]

libphonenumber, a library from Google, parses, formats and validates phone numbers for all countries and regions of the world.[11] libphonenumber's isPossibleNumber guesses whether a number is possible using only its length, while isValidNumber validates it for a region using length and prefix information.[11] E.164 divides a number into a country code of one to three digits and a subscriber number of at most twelve to fourteen digits.[3] The first issue of E.164 was published in 1988.[3]

DuckDB's splink_udfs community extension provides functions for data cleaning and phonetic matching, including soundex, strip_diacritics, unaccent and double_metaphone.[12] Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English, so that names that sound alike get the same code despite minor differences in spelling.[13] Soundex was developed by Robert C. Russell and Margaret King Odell and patented in 1918 and 1922.[13] Under American Soundex, Robert and Rupert both return R163, while Rubin yields R150.[13]

Compared with the alternatives

Postal address verification goes further than standardising, checking that an address is valid and deliverable, and in the United States an address is valid if it is in the USPS Address Management System's list of mailable addresses.[2] Standardise when you need values that compare cleanly, and verify only when it matters whether the address can actually receive mail. (Meridian's view)

Sources

  1. [1]Data cleansing - Wikipediasecondary source
  2. [2]Postal address verification - Wikipediasecondary source
  3. [3]E.164 - Wikipediasecondary source
  4. [4]GitHub - openvenues/libpostal: A C library for parsing/normalizing street addresses around the world. Powered by statistical NLP and open geo data. · GitHubdocumentation
  5. [5]Feature Engineering - Splinkdocumentation
  6. [6]libphonenumber/FALSEHOODS.md at master · google/libphonenumber · GitHubdocumentation
  7. [7]Personal names around the worldsecondary source
  8. [8]Falsehoods Programmers Believe About Names | Kalzumeus Softwaresecondary source
  9. [9]Chapter 12. PostGIS Extrasdocumentation
  10. [10]us_address_standardizer – DuckDB Community Extensionsdocumentation
  11. [11]GitHub - google/libphonenumber: Google's common Java, C++ and JavaScript library for parsing, formatting, and validating international phone numbers. · GitHubdocumentation
  12. [12]splink_udfs – DuckDB Community Extensionsdocumentation
  13. [13]Soundex - Wikipediasecondary source