如何将Description字段的A、B、C值拆分到Desc1/Desc2/Desc3新列？

Hey there! Let's figure out how to split your Description field (which contains values like "A,B,C") into three distinct columns: Desc1 (holding "A"), Desc2 (holding "B"), and Desc3 (holding "C"). The approach varies slightly based on your database system—here are solutions for the most commonly used ones:

MySQL/MariaDB #

Use the SUBSTRING_INDEX function to extract segments based on your delimiter (we'll assume a comma here; replace it with your actual delimiter if different):

Query to View Split Values #

SELECT
  SUBSTRING_INDEX(Description, ',', 1) AS Desc1,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Description, ',', 2), ',', -1) AS Desc2,
  SUBSTRING_INDEX(Description, ',', -1) AS Desc3
FROM your_table;

Add Columns and Update Data #

If you want to permanently add these columns to your table and populate them:

-- Add new columns
ALTER TABLE your_table
ADD COLUMN Desc1 VARCHAR(255),
ADD COLUMN Desc2 VARCHAR(255),
ADD COLUMN Desc3 VARCHAR(255);

-- Populate the new columns
UPDATE your_table
SET
  Desc1 = SUBSTRING_INDEX(Description, ',', 1),
  Desc2 = SUBSTRING_INDEX(SUBSTRING_INDEX(Description, ',', 2), ',', -1),
  Desc3 = SUBSTRING_INDEX(Description, ',', -1);

SQL Server (2016+) #

Leverage STRING_SPLIT with the ordinal parameter (available in SQL Server 2016 and later) to get indexed split values, then use conditional aggregation to map them to columns:

Query to View Split Values #

SELECT 
  t.your_primary_key, -- Replace with your table's primary key/unique identifier
  MAX(CASE WHEN s.ordinal = 1 THEN s.value END) AS Desc1,
  MAX(CASE WHEN s.ordinal = 2 THEN s.value END) AS Desc2,
  MAX(CASE WHEN s.ordinal = 3 THEN s.value END) AS Desc3
FROM your_table t
CROSS APPLY STRING_SPLIT(t.Description, ',', 1) s
GROUP BY t.your_primary_key;

Add Columns and Update Data #

-- Add new columns
ALTER TABLE your_table
ADD Desc1 VARCHAR(255), Desc2 VARCHAR(255), Desc3 VARCHAR(255);

-- Populate columns using a CTE
WITH SplitData AS (
  SELECT 
    your_primary_key,
    MAX(CASE WHEN ordinal = 1 THEN value END) AS D1,
    MAX(CASE WHEN ordinal = 2 THEN value END) AS D2,
    MAX(CASE WHEN ordinal = 3 THEN value END) AS D3
  FROM your_table
  CROSS APPLY STRING_SPLIT(Description, ',', 1)
  GROUP BY your_primary_key
)
UPDATE t
SET t.Desc1 = sd.D1, t.Desc2 = sd.D2, t.Desc3 = sd.D3
FROM your_table t
JOIN SplitData sd ON t.your_primary_key = sd.your_primary_key;

PostgreSQL #

Use STRING_TO_ARRAY to convert the Description string into an array, then access individual elements by index:

Query to View Split Values #

SELECT
  (STRING_TO_ARRAY(Description, ','))[1] AS Desc1,
  (STRING_TO_ARRAY(Description, ','))[2] AS Desc2,
  (STRING_TO_ARRAY(Description, ','))[3] AS Desc3
FROM your_table;

Add Columns and Update Data #

-- Add new columns
ALTER TABLE your_table
ADD COLUMN Desc1 VARCHAR(255),
ADD COLUMN Desc2 VARCHAR(255),
ADD COLUMN Desc3 VARCHAR(255);

-- Populate the new columns
UPDATE your_table
SET
  Desc1 = (STRING_TO_ARRAY(Description, ','))[1],
  Desc2 = (STRING_TO_ARRAY(Description, ','))[2],
  Desc3 = (STRING_TO_ARRAY(Description, ','))[3];

Important Notes #

• Replace ',' in all examples with your actual delimiter (e.g., space ' ', semicolon ';') if your Description uses a different separator.
• If some rows have fewer than three values in Description, the corresponding new column will show NULL. You can use COALESCE to set a default value, like COALESCE((STRING_TO_ARRAY(Description, ','))[3], '') AS Desc3 to replace NULL with an empty string.

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