PostgreSQL中如何按首次出现的空格拆分字符串？

Hey there! Let's solve this string splitting task you need done. The core requirement here is to split each name only at the first occurrence of a space, then wrap each part in pipes like your example shows. Below are solutions for some common tools you might be working with:

Excel / Google Sheets #

If you're using spreadsheets, you can combine text functions to get the desired format. Here's a formula that works for both Excel and Google Sheets:

=CONCAT("|", LEFT(A1, FIND(" ", A1)-1), "| |", RIGHT(A1, LEN(A1)-FIND(" ", A1)), "|")

• FIND(" ", A1) locates the position of the first space in cell A1
• LEFT(A1, FIND(" ", A1)-1) grabs everything before that first space
• RIGHT(A1, LEN(A1)-FIND(" ", A1)) captures everything after the first space
• The CONCAT function stitches it all together in your requested |part1| |part2| format

Note: If some cells might not have a space, add an IF check to handle those cases gracefully:

=IF(ISNUMBER(FIND(" ", A1)), CONCAT("|", LEFT(A1, FIND(" ", A1)-1), "| |", RIGHT(A1, LEN(A1)-FIND(" ", A1)), "|"), "|"&A1&"| | |")

Python (with Pandas for tabular data) #

If you're working with a dataset in Python, Pandas makes this straightforward:

import pandas as pd

# Sample data matching your example
df = pd.DataFrame({
    'name': ['word1 word2', 'word1 word2 word3', 'word1 word2']
})

# Split at the first space into two columns
df[['first_part', 'rest_part']] = df['name'].str.split(' ', n=1, expand=True)

# Create the formatted column
df['formatted_name'] = '|' + df['first_part'] + '| |' + df['rest_part'] + '|'

# View the result
print(df['formatted_name'])

• The n=1 parameter in str.split() ensures we only split once at the first space
• We then combine the two split parts into your required format using string concatenation

SQL (Database Query) #

If you need to do this directly in a database, here are examples for popular systems:

MySQL / MariaDB #

SELECT
  CONCAT(
    '|',
    SUBSTRING_INDEX(name_column, ' ', 1),
    '| |',
    SUBSTRING_INDEX(name_column, ' ', -1),
    '|'
  ) AS formatted_name
FROM your_table;

• SUBSTRING_INDEX(name_column, ' ', 1) gets the portion before the first space
• SUBSTRING_INDEX(name_column, ' ', -1) gets everything after the first space

PostgreSQL #

SELECT
  CONCAT(
    '|',
    SPLIT_PART(name_column, ' ', 1),
    '| |',
    SUBSTRING(name_column FROM POSITION(' ' IN name_column) + 1),
    '|'
  ) AS formatted_name
FROM your_table;

• SPLIT_PART extracts the first segment, and SUBSTRING grabs everything starting right after the first space

All these methods will produce exactly the output you're looking for:

• |word1| |word2|
• |word1| |word2 word3|
• |word1| |word2|

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