如何在SQL Server中将tb1表OppName列的CSV字符串拆分为8列

Hey there! Great job stripping out those square brackets from your tb1 table's OppName column. Now let's split that comma-separated, quoted string into the 8 distinct columns c1 through c8 you need. Below are two reliable approaches depending on your SQL dialect and version:

Approach 1: Using STRING_SPLIT (SQL Server 2016+) #

If you're on a newer SQL Server version that supports STRING_SPLIT, this method is clean and efficient. First, we'll add the target columns to your table, then split each row's value and map them to the correct columns.

Step 1: Add the target columns #

ALTER TABLE tb1 
ADD c1 VARCHAR(255), c2 VARCHAR(255), c3 VARCHAR(255), c4 VARCHAR(255),
    c5 VARCHAR(255), c6 VARCHAR(255), c7 VARCHAR(255), c8 VARCHAR(255);

Step 2: Split and update the columns #

(Note: Replace ID with your table's primary key column if you have one—this ensures each row's splits are mapped correctly to its own columns.)

-- First, remove double quotes to simplify splitting
UPDATE tb1 SET OppName = REPLACE(OppName, '"', '');

WITH SplitData AS (
    SELECT 
        t.ID, -- Use your actual primary key here
        s.value,
        ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY (SELECT NULL)) AS ColumnNumber
    FROM tb1 t
    CROSS APPLY STRING_SPLIT(t.OppName, ',') AS s
)
UPDATE t
SET 
    c1 = (SELECT value FROM SplitData WHERE ID = t.ID AND ColumnNumber = 1),
    c2 = (SELECT value FROM SplitData WHERE ID = t.ID AND ColumnNumber = 2),
    c3 = (SELECT value FROM SplitData WHERE ID = t.ID AND ColumnNumber = 3),
    c4 = (SELECT value FROM SplitData WHERE ID = t.ID AND ColumnNumber = 4),
    c5 = (SELECT value FROM SplitData WHERE ID = t.ID AND ColumnNumber = 5),
    c6 = (SELECT value FROM SplitData WHERE ID = t.ID AND ColumnNumber = 6),
    c7 = (SELECT value FROM SplitData WHERE ID = t.ID AND ColumnNumber = 7),
    c8 = (SELECT value FROM SplitData WHERE ID = t.ID AND ColumnNumber = 8)
FROM tb1 t;

Approach 2: Using SUBSTRING and CHARINDEX (Universal SQL) #

If you're using an older SQL version or a dialect without STRING_SPLIT, this manual substring method works across most databases. It directly extracts each value between the quoted commas:

Step 1: Add the target columns #

Same as above:

ALTER TABLE tb1 
ADD c1 VARCHAR(255), c2 VARCHAR(255), c3 VARCHAR(255), c4 VARCHAR(255),
    c5 VARCHAR(255), c6 VARCHAR(255), c7 VARCHAR(255), c8 VARCHAR(255);

Step 2: Extract each column value #

UPDATE tb1
SET
    -- Extract c1 (first quoted value)
    c1 = SUBSTRING(OppName, CHARINDEX('"', OppName) + 1, CHARINDEX('","', OppName) - CHARINDEX('"', OppName) - 1),
    -- Extract c2 (second quoted value)
    c2 = SUBSTRING(OppName, CHARINDEX('","', OppName) + 3, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1) - CHARINDEX('","', OppName) - 3),
    -- Extract c3 (third quoted value)
    c3 = SUBSTRING(OppName, 
                   CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1) + 3, 
                   CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1) - CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1) - 3),
    -- Extract c4 (fourth quoted value)
    c4 = SUBSTRING(OppName, 
                   CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1) + 3, 
                   CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1)+1) - CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1) - 3),
    -- Extract c5 (fifth quoted value)
    c5 = SUBSTRING(OppName, 
                   CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1)+1) + 3, 
                   CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1)+1)+1) - CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1)+1) - 3),
    -- Extract c6 (sixth quoted value)
    c6 = SUBSTRING(OppName, 
                   CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1)+1)+1) + 3, 
                   CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1)+1)+1)+1) - CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1)+1)+1) - 3),
    -- Extract c7 (seventh quoted value)
    c7 = SUBSTRING(OppName, 
                   CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1)+1)+1)+1) + 3, 
                   CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1)+1)+1)+1)+1) - CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName, CHARINDEX('","', OppName)+1)+1)+1)+1)+1) - 3),
    -- Extract c8 (eighth quoted value, last in the string)
    c8 = SUBSTRING(OppName, 
                   LEN(OppName) - CHARINDEX('","', REVERSE(OppName)) - 1, 
                   CHARINDEX('"', REVERSE(OppName)) - 1);

Both methods will map your CSV values to c1 through c8 exactly as you need—c1 gets "OpportunityName", c2 gets "Fact", and so on up to c8 with "UpdateDate".

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