如何在列值中匹配国家代码并关联两表查询（处理误匹配）

Got it, let's tackle this problem step by step. The core challenge here is accurately matching country codes from the terminal_location field without false positives—like avoiding matches where a code (e.g., KZ) appears as a substring in another word (e.g., "Redkzsuzin") instead of being a standalone identifier.

Assumptions About Your Tables #

First, let's define the table structures we'll work with (adjust names if yours differ):

• country_codes: Stores country identifiers, with columns country_id (numeric ID, e.g., 398) and country_code (2-letter string, e.g., KZ).
• tranzactions: Stores transaction data, with a terminal_location field containing free-text location strings.

Key Approach: Use Word Boundary Regular Expressions #

To avoid matching substrings, we'll use word boundary regex patterns to ensure we only match country codes that are standalone tokens (not part of longer words). The exact syntax varies slightly by database, so here are examples for the two most common systems:

1. MySQL/MariaDB Solution

MySQL uses [[:<:]] and [[:>:]] to denote word boundaries. We'll concatenate these with each country code to create a regex that matches only standalone instances:

SELECT
    cc.country_id,
    cc.country_code,
    t.terminal_location
FROM
    tranzactions t
JOIN
    country_codes cc ON t.terminal_location REGEXP CONCAT('[[:<:]]', cc.country_code, '[[:>:]]');

2. PostgreSQL Solution

PostgreSQL uses \m (start of word) and \M (end of word) for word boundaries. Use ~* for case-insensitive matching (or ~ if you need strict case sensitivity):

SELECT
    cc.country_id,
    cc.country_code,
    t.terminal_location
FROM
    tranzactions t
JOIN
    country_codes cc ON t.terminal_location ~* CONCAT('\m', cc.country_code, '\M');

How This Solves the False Positive Problem #

Let's test with your tricky example: 'Gucci Moscow Redkzsuzin district RU'

• The regex for KZ would look for [[:<:]]KZ[[:>:]] (MySQL) or \mKZ\M (PostgreSQL). Since "KZ" is embedded in "Redkzsuzin", it's not a standalone word—so no match.
• The regex for RU matches the standalone "RU" at the end of the string, so it correctly associates this record with the RU country code.

Handling Edge Cases #

• Multiple country codes in one location: If a terminal_location has more than one valid country code (e.g., 'US Starbucks KZ'), this query will return two rows for that transaction (one for US, one for KZ). If you only need the last occurring code, you'll need additional logic—for example, in MySQL, you could use SUBSTRING_INDEX to extract the last word and match it to country_code.
• Case sensitivity: The examples use case-insensitive matching (PostgreSQL's ~*, MySQL's REGEXP is case-insensitive by default). If your country codes are always uppercase and you want strict matching, adjust the regex to enforce uppercase (e.g., CONCAT('[[:<:]]', UPPER(cc.country_code), '[[:>:]]')).

内容的提问来源于stack exchange，提问作者Makhmut Syzdykbayev