SQL字符串分段实现求助：将长字符串分割为不拆单词的40字符段落

Got it, let's fix this address splitting logic so it's clean, scalable, and handles all segments automatically instead of manually calculating each position. Your core idea of finding the last space before the 40-character limit is correct—we just need to wrap that logic in a recursive CTE to handle all segments without hardcoding positions.

Final Working Code #

DECLARE @test VARCHAR(150) 
SET @test = 'GTB INDUSTRIAL NETWORK DAVAO I GTB INDUSTRIAL NETWORK DAVAO I DOOR 10 2F SJRDC BLDG PHASE 1 INSULAR SUBD LANANG'

;WITH AddressSegments AS (
    -- Anchor: Get the first segment
    SELECT 
        1 AS SegmentNumber,
        -- Cut at last space before 40 chars to avoid word splits
        CASE 
            WHEN CHARINDEX(' ', REVERSE(SUBSTRING(@test, 1, 40))) > 0 THEN
                SUBSTRING(@test, 1, 40 - CHARINDEX(' ', REVERSE(SUBSTRING(@test, 1, 40))))
            ELSE
                SUBSTRING(@test, 1, 40) -- Fallback if no space in first 40 chars
        END AS Segment,
        -- Calculate start position for next segment
        CASE 
            WHEN CHARINDEX(' ', REVERSE(SUBSTRING(@test, 1, 40))) > 0 THEN
                40 - CHARINDEX(' ', REVERSE(SUBSTRING(@test, 1, 40))) + 1
            ELSE
                41
        END AS NextStartPos
    UNION ALL
    -- Recursive: Get subsequent segments until end of string
    SELECT 
        SegmentNumber + 1,
        CASE 
            WHEN CHARINDEX(' ', REVERSE(SUBSTRING(@test, NextStartPos, 40))) > 0 THEN
                SUBSTRING(@test, NextStartPos, 40 - CHARINDEX(' ', REVERSE(SUBSTRING(@test, NextStartPos, 40))))
            ELSE
                SUBSTRING(@test, NextStartPos, 40)
        END,
        CASE 
            WHEN CHARINDEX(' ', REVERSE(SUBSTRING(@test, NextStartPos, 40))) > 0 THEN
                NextStartPos + (40 - CHARINDEX(' ', REVERSE(SUBSTRING(@test, NextStartPos, 40)))) + 1
            ELSE
                NextStartPos + 41
        END
    FROM AddressSegments
    WHERE NextStartPos <= LEN(@test)
)
-- Pivot segments into ADDRESS 1-4 columns
SELECT 
    MAX(CASE WHEN SegmentNumber = 1 THEN Segment END) AS [ADDRESS 1],
    MAX(CASE WHEN SegmentNumber = 2 THEN Segment END) AS [ADDRESS 2],
    MAX(CASE WHEN SegmentNumber = 3 THEN Segment END) AS [ADDRESS 3],
    MAX(CASE WHEN SegmentNumber = 4 THEN Segment END) AS [ADDRESS 4]
FROM AddressSegments;

How It Works #

1. Anchor Member: Handles the first segment by:
   • Grabbing the first 40 characters of the string
   • Reversing that substring to find the first space (which maps to the last space in the original 40-char chunk)
   • Cutting the segment at that space to avoid splitting words
2. Recursive Member: Repeats the anchor logic for each subsequent segment, using the end position of the previous segment as the start for the next one. This continues until we pass the end of the input string.
3. Pivot Final Result: Converts the row-based segments into the requested ADDRESS 1 to ADDRESS 4 columns.

Test Output for Your String #

ADDRESS 1: GTB INDUSTRIAL NETWORK DAVAO I GTB INDUSTRIAL
ADDRESS 2: NETWORK DAVAO I DOOR 10 2F SJRDC BLDG PHASE 1
ADDRESS 3: INSULAR SUBD LANANG
ADDRESS 4: NULL

If your input string requires a 4th segment, the CTE will automatically generate it without any changes to the code.

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