SQL实现单字段拆分多注释：如何拆分表中合并的注释内容

Got it, let's figure out how to split those concatenated notes into individual rows using SQL. Your field has notes separated by numbered markers like 1), 2), etc.—here are practical approaches tailored to common SQL dialects:

SQL Server (2016+) #

If you're on SQL Server, you can use STRING_SPLIT combined with string replacement to standardize the delimiters first. For a dynamic solution that handles any number of markers:

WITH RecursiveReplace AS (
    SELECT 
        NotesField AS ModifiedNotes,
        PATINDEX('%[0-9])%', NotesField) AS MarkerPos
    FROM YourTable -- Replace with your actual table name
    UNION ALL
    SELECT 
        STUFF(ModifiedNotes, MarkerPos, 2, '|') AS ModifiedNotes,
        PATINDEX('%[0-9])%', ModifiedNotes) AS MarkerPos
    FROM RecursiveReplace
    WHERE MarkerPos > 0
)
SELECT 
    TRIM(value) AS IndividualNote
FROM RecursiveReplace
WHERE MarkerPos = 0 -- Grab the final string with all markers replaced
CROSS APPLY STRING_SPLIT(ModifiedNotes, '|')
WHERE TRIM(value) <> ''; -- Filter out empty rows from leading/trailing splits

Note: If your notes might contain 数字) as part of normal text (e.g., "See ref 3) for details"), adjust the PATINDEX pattern to only match markers at the start of a segment (like combining with CHARINDEX to check for preceding whitespace).

MySQL 8.0+ #

MySQL supports recursive CTEs and regex functions in newer versions. Use REGEXP_SUBSTR to extract each note segment:

WITH RECURSIVE NumberSequence AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM NumberSequence WHERE n <= 10 -- Adjust max to your expected number of notes
)
SELECT 
    TRIM(REGEXP_SUBSTR(t.NotesField, '[0-9])(.*?)(?=[0-9])|.*$', 1, n, 's')) AS IndividualNote
FROM YourTable t
JOIN NumberSequence ns ON ns.n <= REGEXP_COUNT(t.NotesField, '[0-9])') + 1
WHERE TRIM(REGEXP_SUBSTR(t.NotesField, '[0-9])(.*?)(?=[0-9])|.*$', 1, n, 's')) <> '';

The regex [0-9])(.*?)(?=[0-9])|.*$ matches each segment between markers, or the final segment without a trailing marker.

PostgreSQL #

PostgreSQL has a handy REGEXP_SPLIT_TO_TABLE function that simplifies this:

SELECT 
    TRIM(note_segment) AS IndividualNote
FROM YourTable,
REGEXP_SPLIT_TO_TABLE(YourTable.NotesField, '[0-9])') AS note_segment
WHERE TRIM(note_segment) <> '';

This splits the string at every 数字) marker, then trims whitespace and filters out empty results.

Key Considerations #

• Performance: For large datasets, recursive CTEs might be slower than using a pre-generated numbers table. Create a small numbers table with enough rows to cover your maximum number of notes if needed.
• Edge Cases: If your notes have inconsistent marker formatting (e.g., 1 ) instead of 1)), adjust the regex or replacement logic to match your actual data format.

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