Pattern: Country

“Country” is one of the patterns that you can select on the Match panel. Use this pattern to make a field match a country code or country name as defined in the ISO 3166 standard. You can restrict the field to the codes or names of specific countries.

Suppose you have a database of orders for a company that sells worldwide. The database stores a two-letter ISO country code for each order, indicating the customer’s country. You want to retrieve all orders from customers based in the European Union. You want to create a regular expression that matches the country codes of all the EU countries. You can find this example as “Pattern: country” in the RegexMagic library.

  1. Click the New Formula button on the top toolbar to clear out all settings on the Samples, Match, and Action panels.
  2. On the Samples panel, paste in one new sample:
    US
    CA
    GB
    DE
    FR
  3. On the Match panel, select GB in the sample text and click the Mark button. RegexMagic automatically adds a field using the country pattern. The option “two-letter codes” is automatically selected along with United Kingdom in the list of countries.
  4. Tick European Union in the list of country groups. This automatically selects all 26 EU member states. The NATO and OECD boxes are filled in to indicate that some but not all of the countries in these groups are selected. Some EU member states are also members of NATO and the OECD.



  5. On the Regex panel, select “C# (.NET 2.0–7.0)” as your application, turn off free-spacing, and turn off mode modifiers. Click the Generate button, and you’ll get this regular expression:
    AT|B[EG]|C[YZ]|D[EK]|E[ES]|F[IR]|G[BR]|H[RU]|I[ET]|L[TUV]|MT|NL|P[LT]|RO|S[EIK]

    Required options: Case insensitive.
    Unused options: Exact spacing; Dot doesn’t match line breaks; ^$ don’t match at line breaks; Numbered capture.

  6. The Samples panel now highlights the country codes that belong to EU member countries, but not those that belong to other countries:
    US
    CA
    GB
    DE
    FR
  7. On the Use panel, select a database such as MySQL, Oracle, or PostgreSQL in the Language drop-down list.
  8. In the Function drop-down list, choose “select rows in which a column is entirely matched by the regex”.
  9. Enter the name of table you’re selecting rows from and the name of the column with the country code to generate a SQL statement for selecting all EU orders.

Country Names with Alternatives

The ISO 3166 standard lists one name in English and one name in French for each country. These are the country names that are built into the “country” pattern in RegexMagic. But many countries are known by various names. Some of the choices made by the ISO committee are very controversial. This example shows how you can generate a regular expression that matches all ISO 3166 names in addition to a list of your own alternative names. You could use this regex to validate country names printed on shipping labels, without forcing the user to adhere to the ISO standard. E.g. you could allow USA and UNITED STATES OF AMERICA in addition to UNITED STATES as specified by ISO. You can find this example as “Pattern: country (alternatives)” in the RegexMagic library.

  1. Click the New Formula button on the top toolbar to clear out all settings on the Samples, Match, and Action panels.
  2. Click the Add First Field button to add field 1.
  3. In the “pattern to match field” drop-down list, select “country”.
  4. Right-click on the list of countries and select Check All.
  5. Tick “English names”, “French names”, and “make suffixes optional” to allow all ISO country names.
  6. If you generate the regular expression now, you’ll get a very long list of country names delimited by vertical bars (the alternation operator in regular expressions).
  7. Select “alternation” in the “kind of field” drop-down list for field 1. This automatically moves the country pattern we just created into a new field 2 that becomes the first alternative of field 1.
  8. Click the Add Last Sub-Field button Add Last Sub-Field to add field 3 as the last field under field 1.
  9. In the “pattern to match field” drop-down list, select “list of fixed values”.
  10. Enter the list of additional country names, one on each line. You can add as many as you like. E.g. enter USA, press Enter to start a new line, and enter UNITED STATES OF AMERICA.
  11. If you generate the regular expression again, you’ll notice RegexMagic adds the additional country names to the end of the list.

Related Examples

Reference