Salesforce Marketing Cloud中SQL查询特定前缀邮编联系人返回0结果求助

Hey there, let's break down why your query isn't pulling in those expected records and get it working properly.

The Core Issue #

Your current query uses IN with wildcard characters (%), which doesn't work the way you think it does. The IN clause is designed for exact value matches—so when you write Postal_Code IN ('L5H%','K2S%','L3S%'), the SQL engine is looking for records where the postal code is literally equal to L5H%, K2S%, or L3S% (not just starting with those prefixes). That's why you're getting zero results even though matching data exists.

Correct Query Options #

Here are two reliable ways to get the prefix-matching behavior you need:

Option 1: Multiple LIKE Statements with OR

This is the most straightforward, widely supported approach in SFMC:

SELECT * 
FROM [customer_list_DE] 
WHERE Postal_Code LIKE 'L5H%'
OR Postal_Code LIKE 'K2S%'
OR Postal_Code LIKE 'L3S%'

Option 2: Regular Expression Matching (If Supported)

If your SFMC environment allows regex, you can simplify the condition with REGEXP_LIKE:

SELECT * 
FROM [customer_list_DE] 
WHERE REGEXP_LIKE(Postal_Code, '^(L5H|K2S|L3S)')

The ^ ensures we're matching the start of the string, and the group (L5H|K2S|L3S) covers all your target prefixes.

Bonus Troubleshooting Checks #

If you still don't get results after fixing the query, double-check these:

• Hidden spaces: Postal code fields sometimes have leading/trailing whitespace. Try trimming the field first:

  SELECT * 
  FROM [customer_list_DE] 
  WHERE LTRIM(RTRIM(Postal_Code)) LIKE 'L5H%'
  OR LTRIM(RTRIM(Postal_Code)) LIKE 'K2S%'
  OR LTRIM(RTRIM(Postal_Code)) LIKE 'L3S%'
  

• Field/DE name accuracy: Confirm Postal_Code is the exact field name, and [customer_list_DE] is the correct Data Extension name (including brackets if needed).
• Case sensitivity: While SFMC is generally case-insensitive for string matches, if your postal codes have mixed case, you can standardize with UPPER() or LOWER():

  SELECT * 
  FROM [customer_list_DE] 
  WHERE UPPER(Postal_Code) LIKE 'L5H%'
  OR UPPER(Postal_Code) LIKE 'K2S%'
  OR UPPER(Postal_Code) LIKE 'L3S%'
  

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