使用split_part拆分韩语地址字符串失效，请求技术协助

Hey there! I see you're having trouble using split_part with a Korean address string—even though it worked perfectly for Japanese data. Let's break down why this is happening and how to fix it quickly.

The Root Cause #

Your current query uses chr(12288) (the full-width space character, U+3000) as the delimiter. The issue is simple: the spaces in your Korean address probably aren't full-width spaces. Japanese text often relies on full-width spaces, but Korean addresses commonly use standard half-width spaces (U+0020, ASCII code 32) or other Unicode whitespace characters instead.

Step 1: Identify the Exact Space Character #

First, let's confirm what kind of space is actually in your string. Run this query to get the Unicode code point of the space:

SELECT unicode(substring('중국 베이징 하이디안 디스트릭트', 4, 1)) AS space_code_point;

• If it returns 32, that's a standard half-width space.
• If it returns 160, that's a non-breaking space (U+00A0).
• If it returns 12288, we’ll need to dig deeper, but that’s unlikely here.

Step 2: Use the Correct Delimiter #

Once you know the code point, update your split_part call with the matching chr() value:

• For half-width spaces (code 32):

  SELECT split_part('중국 베이징 하이디안 디스트릭트', chr(32), 1);
  

• For non-breaking spaces (code 160):

  SELECT split_part('중국 베이징 하이디안 디스트릭트', chr(160), 1);
  

Step 3: A More Robust Alternative (Handle All Whitespace) #

If you want a solution that works no matter what type of whitespace is in the string (full-width, half-width, non-breaking, etc.), use PostgreSQL's regex functions instead. Here are two reliable options:

1. Split into an array and grab the first element:

   SELECT (regexp_split_to_array('중국 베이징 하이디안 디스트릭트', '\s+'))[1];
   

2. Replace everything after the first whitespace with an empty string:

   SELECT regexp_replace('중국 베이징 하이디안 디스트릭트', '\s.*', '');
   

The \s+ pattern matches one or more whitespace characters (any Unicode whitespace), so this will handle all common space types in one go.

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