SQL Server存储过程开发：根据分隔符数量拆分字段插入目标表

Got it, let's work through this problem together. You need to split that backslash-delimited string from your staging table and insert the parts into different columns of your real business table—depending on whether there are 8 or 9 delimiters (which means 9 or 10 substrings total). Here's how to pull this off in common SQL databases:

For SQL Server #

First, we can calculate the number of backslashes using LEN(column) - LEN(REPLACE(column, '\', ''))—this gives us the exact count of delimiters. Then we'll split the string while preserving the order of substrings, and map each part to the correct target columns.

Step-by-Step Code #

-- Insert records with 8 delimiters (9 substrings) into the first set of columns
INSERT INTO realTable (col1, col2, col3, col4, col5, col6, col7, col8, col9)
SELECT 
    MAX(CASE WHEN seq = 1 THEN value END) AS col1,
    MAX(CASE WHEN seq = 2 THEN value END) AS col2,
    MAX(CASE WHEN seq = 3 THEN value END) AS col3,
    MAX(CASE WHEN seq = 4 THEN value END) AS col4,
    MAX(CASE WHEN seq = 5 THEN value END) AS col5,
    MAX(CASE WHEN seq = 6 THEN value END) AS col6,
    MAX(CASE WHEN seq = 7 THEN value END) AS col7,
    MAX(CASE WHEN seq = 8 THEN value END) AS col8,
    MAX(CASE WHEN seq = 9 THEN value END) AS col9
FROM stagingTable
CROSS APPLY (
    -- Split the string and assign a sequence number to preserve order
    SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
    FROM STRING_SPLIT(stagingColumn, '\')
) AS splitVals
-- Filter for records with exactly 8 delimiters
WHERE (LEN(stagingColumn) - LEN(REPLACE(stagingColumn, '\', ''))) = 8
-- Group by your staging table's unique ID to keep each original record's parts together
GROUP BY stagingTable.id;

-- Insert records with 9 delimiters (10 substrings) into the second set of columns
INSERT INTO realTable (colA, colB, colC, colD, colE, colF, colG, colH, colI, colJ)
SELECT 
    MAX(CASE WHEN seq = 1 THEN value END) AS colA,
    MAX(CASE WHEN seq = 2 THEN value END) AS colB,
    MAX(CASE WHEN seq = 3 THEN value END) AS colC,
    MAX(CASE WHEN seq = 4 THEN value END) AS colD,
    MAX(CASE WHEN seq = 5 THEN value END) AS colE,
    MAX(CASE WHEN seq = 6 THEN value END) AS colF,
    MAX(CASE WHEN seq = 7 THEN value END) AS colG,
    MAX(CASE WHEN seq = 8 THEN value END) AS colH,
    MAX(CASE WHEN seq = 9 THEN value END) AS colI,
    MAX(CASE WHEN seq = 10 THEN value END) AS colJ
FROM stagingTable
CROSS APPLY (
    SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
    FROM STRING_SPLIT(stagingColumn, '\')
) AS splitVals
WHERE (LEN(stagingColumn) - LEN(REPLACE(stagingColumn, '\', ''))) = 9
GROUP BY stagingTable.id;

For MySQL #

MySQL uses different string functions, but the core logic is the same: count delimiters, split the string, and map parts to target columns. We'll use SUBSTRING_INDEX to extract each substring by position.

Step-by-Step Code #

-- Insert records with 8 delimiters (9 substrings)
INSERT INTO realTable (col1, col2, col3, col4, col5, col6, col7, col8, col9)
SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 1), '\', -1) AS col1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 2), '\', -1) AS col2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 3), '\', -1) AS col3,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 4), '\', -1) AS col4,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 5), '\', -1) AS col5,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 6), '\', -1) AS col6,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 7), '\', -1) AS col7,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 8), '\', -1) AS col8,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 9), '\', -1) AS col9
FROM stagingTable
WHERE (CHAR_LENGTH(stagingColumn) - CHAR_LENGTH(REPLACE(stagingColumn, '\', ''))) = 8;

-- Insert records with 9 delimiters (10 substrings)
INSERT INTO realTable (colA, colB, colC, colD, colE, colF, colG, colH, colI, colJ)
SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 1), '\', -1) AS colA,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 2), '\', -1) AS colB,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 3), '\', -1) AS colC,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 4), '\', -1) AS colD,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 5), '\', -1) AS colE,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 6), '\', -1) AS colF,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 7), '\', -1) AS colG,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 8), '\', -1) AS colH,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 9), '\', -1) AS colI,
    SUBSTRING_INDEX(SUBSTRING_INDEX(stagingColumn, '\', 10), '\', -1) AS colJ
FROM stagingTable
WHERE (CHAR_LENGTH(stagingColumn) - CHAR_LENGTH(REPLACE(stagingColumn, '\', ''))) = 9;

Key Notes #

• Unique Identifier: For SQL Server, make sure your staging table has a unique ID column (like id) to group split substrings back to their original record.
• Empty Substrings: If your strings might have empty parts (e.g., text\\moretext), you can add logic to replace empty values with NULL if needed (e.g., NULLIF(value, '') in SQL Server, NULLIF(SUBSTRING_INDEX(...), '') in MySQL).
• Testing: Always test with sample data first to confirm the split and insertion works as expected before running on production data.

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