如何让split_part支持多分隔符（空格/逗号）？技术求助

Got it, let's break down how to solve both your multi-delimiter split issue and the suffix matching problem.

First, the straightforward truth: PostgreSQL's split_part() function doesn't natively support multiple delimiters. But the good news is we can work around this with regex functions, which also help us clean up those CPA/CFA/CIMA suffixes messing up your matches.

Step 1: Handle Multiple Delimiters (Space + Comma) #

Instead of using split_part() directly, we can use regexp_split_to_array() which accepts regular expressions for delimiters. This lets us split on either spaces or commas (even multiple consecutive ones) in one go.

For example, to get the first part of advisor_last_name split by spaces or commas:

lower(trim((regexp_split_to_array(t.advisor_last_name, '[, ]+'))[1]))

• '[, ]+' is the regex pattern: it matches one or more (+) commas , or spaces .
• regexp_split_to_array() turns the string into an array split by those delimiters
• [1] grabs the first element of the array (PostgreSQL arrays are 1-indexed)
• trim() removes any leftover leading/trailing whitespace, lower() standardizes the case for consistent matching

Step 2: Remove Annoying Suffixes (CPA/CFA/CIMA) #

To eliminate those professional suffixes before splitting, add a regexp_replace() call to strip them out first. We'll target suffixes that are preceded by whitespace and are whole words (to avoid accidentally matching parts of actual names).

Here's the combined query that handles both suffixes and multi-delimiters:

lower(trim(
  (regexp_split_to_array(
    regexp_replace(t.advisor_last_name, '\s+(CPA|CFA|CIMA)\b', '', 'gi'),
    '[, ]+'
  ))[1]
))

• regexp_replace(..., '\s+(CPA|CFA|CIMA)\b', '', 'gi'):
  • \s+ matches one or more spaces before the suffix
  • (CPA|CFA|CIMA) lists the suffixes you want to remove (add more inside the parentheses if needed, like CFP or EA)
  • \b ensures we only match whole words (so we don't strip "CFA" from a name like "Carly")
  • gi flags make the match case-insensitive (i) and global (g) to remove all instances

Alternative Approach (Sticking with split_part()) #

If you prefer using split_part() instead of arrays, you can first normalize all delimiters to a single space, then use your original function as before:

lower(trim(
  split_part(
    regexp_replace(
      regexp_replace(t.advisor_last_name, '\s+(CPA|CFA|CIMA)\b', '', 'gi'),
      '[, ]+', ' ', 'g'
    ),
    ' ', 1
  )
))

• This first strips suffixes, then replaces all commas/spaces with single spaces, then splits on that single space to get the first part.

Edge Cases to Keep in Mind #

• If some names have hyphens (e.g., "Smith-Jones") and you don't want to split those, make sure your regex doesn't include hyphens.
• If suffixes are sometimes separated by commas (e.g., "Smith, CPA"), the regex still works because we first strip the suffixes before normalizing delimiters.

Let me know if you need to tweak this for other specific patterns in your data!

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