SQL按分隔符指定次数拆分字符串至列及多格式数据处理需求

Hey there! Let's work through these two SQL string manipulation tasks step by step. I'll cover solutions for the most common databases (MySQL, PostgreSQL, SQL Server) since syntax can differ a bit across systems.

The core idea here is to split a string at specific occurrences of your delimiter, and fill in default values if the split part doesn't exist. Let's use a sample scenario: suppose we have a column data_str with values like a,b,c,d or x,y, and we want to split it at the 1st, 2nd, 3rd commas into columns col1, col2, col3, using 'N/A' as the default when a part is missing.

MySQL Solution #

MySQL has the handy SUBSTRING_INDEX() function that makes this straightforward. We can nest it to get the parts we need, and use IFNULL() or COALESCE() to add defaults:

SELECT
  -- Get everything before the 1st comma
  IFNULL(SUBSTRING_INDEX(data_str, ',', 1), 'N/A') AS col1,
  -- Get the part between 1st and 2nd comma
  IFNULL(
    SUBSTRING_INDEX(SUBSTRING_INDEX(data_str, ',', 2), ',', -1),
    'N/A'
  ) AS col2,
  -- Get the part between 2nd and 3rd comma
  IFNULL(
    SUBSTRING_INDEX(SUBSTRING_INDEX(data_str, ',', 3), ',', -1),
    'N/A'
  ) AS col3
FROM your_table;

PostgreSQL Solution #

PostgreSQL uses STRING_TO_ARRAY() to turn the string into an array, then we can access array elements directly. Use COALESCE() for defaults:

SELECT
  COALESCE((STRING_TO_ARRAY(data_str, ','))[1], 'N/A') AS col1,
  COALESCE((STRING_TO_ARRAY(data_str, ','))[2], 'N/A') AS col2,
  COALESCE((STRING_TO_ARRAY(data_str, ','))[3], 'N/A') AS col3
FROM your_table;

If you need to handle whitespace around delimiters (like a, b, c), add TRIM() to clean up the values:

COALESCE(TRIM((STRING_TO_ARRAY(data_str, ','))[1]), 'N/A') AS col1

SQL Server Solution #

SQL Server has STRING_SPLIT() but it returns rows, not columns. Instead, we can use a cleaner approach with OPENJSON (requires SQL Server 2016+):

SELECT
  COALESCE(json_data.col1, 'N/A') AS col1,
  COALESCE(json_data.col2, 'N/A') AS col2,
  COALESCE(json_data.col3, 'N/A') AS col3
FROM your_table
CROSS APPLY (
  SELECT
    MAX(CASE WHEN idx = 1 THEN value END) AS col1,
    MAX(CASE WHEN idx = 2 THEN value END) AS col2,
    MAX(CASE WHEN idx = 3 THEN value END) AS col3
  FROM (
    SELECT TRIM(value) AS value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS idx
    FROM STRING_SPLIT(data_str, ',')
  ) AS split_data
) AS json_data;

For this task, we have a column (let's call it source_col) with three formats:

• Empty string
• Single numeric string (e.g., 2.05)
• 12 numeric values separated by commas (e.g., '1.01, 2.02, 3.03, 4.04, 5.05, 6.06, 7.07, 8.08, 9.09, 10.10, 11.11, 12.12')

We need to split the 12-value strings into 4 columns (each containing 3 values) and insert them into another table (target_table with columns col1, col2, col3, col4).

First, we'll filter rows to only include the 12-value format by counting commas (12 values mean 11 commas).

MySQL Solution #

INSERT INTO target_table (col1, col2, col3, col4)
SELECT
  -- First 3 values: everything before the 3rd comma
  TRIM(SUBSTRING_INDEX(source_col, ',', 3)) AS col1,
  -- Next 3 values: between 3rd and 6th comma
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(source_col, ',', 6), ',', -3)) AS col2,
  -- Next 3 values: between 6th and 9th comma
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(source_col, ',', 9), ',', -3)) AS col3,
  -- Last 3 values: everything after the 9th comma
  TRIM(SUBSTRING_INDEX(source_col, ',', -3)) AS col4
FROM source_table
WHERE source_col != '' 
  AND LENGTH(source_col) - LENGTH(REPLACE(source_col, ',', '')) = 11; -- 11 commas = 12 values

PostgreSQL Solution #

Use STRING_TO_ARRAY() to split into an array, then slice the array and join back into strings:

INSERT INTO target_table (col1, col2, col3, col4)
SELECT
  TRIM(ARRAY_TO_STRING((STRING_TO_ARRAY(source_col, ', '))[1:3], ', ')) AS col1,
  TRIM(ARRAY_TO_STRING((STRING_TO_ARRAY(source_col, ', '))[4:6], ', ')) AS col2,
  TRIM(ARRAY_TO_STRING((STRING_TO_ARRAY(source_col, ', '))[7:9], ', ')) AS col3,
  TRIM(ARRAY_TO_STRING((STRING_TO_ARRAY(source_col, ', '))[10:12], ', ')) AS col4
FROM source_table
WHERE source_col != ''
  AND array_length(STRING_TO_ARRAY(source_col, ', '), 1) = 12;

Note: I used ', ' as the delimiter here to handle the space after commas in your example. Adjust if your actual data uses just commas.

SQL Server Solution #

Using STRING_SPLIT with row numbers to group values into chunks of 3:

INSERT INTO target_table (col1, col2, col3, col4)
SELECT
  MAX(CASE WHEN chunk = 1 THEN group_vals END) AS col1,
  MAX(CASE WHEN chunk = 2 THEN group_vals END) AS col2,
  MAX(CASE WHEN chunk = 3 THEN group_vals END) AS col3,
  MAX(CASE WHEN chunk = 4 THEN group_vals END) AS col4
FROM (
  SELECT
    source_col,
    -- Group into chunks of 3
    CEILING(ROW_NUMBER() OVER (PARTITION BY source_col ORDER BY (SELECT NULL)) / 3.0) AS chunk,
    -- Concatenate values in each chunk
    STRING_AGG(TRIM(value), ', ') AS group_vals
  FROM source_table
  CROSS APPLY STRING_SPLIT(source_col, ',')
  WHERE source_col != ''
    AND (SELECT COUNT(*) FROM STRING_SPLIT(source_col, ',')) = 12
  GROUP BY source_col, CEILING(ROW_NUMBER() OVER (PARTITION BY source_col ORDER BY (SELECT NULL)) / 3.0)
) AS grouped_data
GROUP BY source_col;

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