SQL按指定长度拆分字符串：支持空格与连字符并转多列

需要实现字符串拆分，满足两个核心需求：

• 在指定长度（如25或30字符）内，同时将空格和连字符“-”作为拆分符，不能拆分单词；
• 拆分后的片段按id分组，分别放入连续的多列中（如fragment 1、fragment 2等）。

现有代码基于按空格拆分的方案改造，但无法同时支持连字符拆分，且无法正确将拆分结果转换为目标多列格式。

CREATE TABLE test (
  id int,
  base_string_column VARCHAR(MAX)
);

INSERT INTO test
VALUES
(4, 'The Incredibly Creatures Who Stopped Living And Accidentally Became Mixed-Up Zombies'),
(15, 'Cafeteria or How Are You Going to Keep Her Down on the Farm after She’s Seen Paris Twice Unknown'),
(16, 'The Saga of the Viking Women and their Voyage to the Waters of the Great Sea Serpent Roger');

拆分逻辑代码 #

DECLARE @chars BIGINT = 25;

WITH
  parts AS
(
  SELECT
    id,
    base_string_column,
    LEN(base_string_column) AS length,
    CAST(0 AS BIGINT) AS last_space,
    CAST(1 AS BIGINT) AS next,
    base_string_column AS fragment
    
  FROM
    test

  UNION ALL

  SELECT
    id,
    parts.base_string_column,
    parts.length,
    last_space.pos,
    parts.next + COALESCE(last_space.pos, @chars),
    SUBSTRING(parts.base_string_column, parts.next, COALESCE(last_space.pos - 1, @chars))
    
  FROM
    parts
  CROSS APPLY
  (
    SELECT
      @chars + 2
      -
      NULLIF(
        CHARINDEX(
          ' ',
          REVERSE(
            SUBSTRING(
              parts.base_string_column + ' ',
              parts.next,
              @chars + 1
            )
          )
        )
        , 0
      )
  )
    last_space(pos)
  WHERE
    parts.next <= parts.length
)

SELECT
  *, len(fragment) AS chars
INTO #outcome_part_1
FROM
  parts
WHERE
  next > 1
ORDER BY
  base_string_column,
  next

多列转换尝试代码 #

SELECT *,
  LAG(next, 1) OVER (PARTITION BY id ORDER BY "some unknown column that perhaps would help" DESC) AS Next_place_after_split
INTO #outcome_part_2
FROM #outcome_part_1

SELECT *,
  CASE WHEN Next_place_after_split IS NULL THEN fragment ELSE '' END AS Column1,
  CASE WHEN Next_place_after_split < next THEN fragment ELSE '' END AS Column2,
  CASE WHEN Next_place_after_split < next THEN fragment ELSE '' END AS Column3,
  CASE WHEN Next_place_after_split < next THEN fragment ELSE '' END AS Column4
FROM #outcome_part_2

| id| base_string_column                                                                             |length|last_space|next|fragment                 |chars|Next_place_after_split | Column1                   | Column2                   | Column3                   | Column5                  | 
| 4 |The Incredibly Creatures Who Stopped Living And Accidentally Became Mixed-Up Zombies            |84    |25        |26  |The Incredibly Creatures |24   |NULL                   | Cafeteria or How Are You  |                           |                           |                          |
| 4 |The Incredibly Creatures Who Stopped Living And Accidentally Became Mixed-Up Zombies            |84    |23        |46  |Who Stopped Living And   |22   |26                     |                           | Who Stopped Living And    | Who Stopped Living And    | Who Stopped Living And   |
| 4 |The Incredibly Creatures Who Stopped Living And Accidentally Became Mixed-Up Zombies            |84    |20        |69  |Accidentally Became      |19   |49                     |                           | Accidentally Became       | Accidentally Became       | Accidentally Became      |
| 4 |The Incredibly Creatures Who Stopped Living And Accidentally Became Mixed-Up Zombies            |84    |26        |95  |Mixed-Up Zombies         |16   |69                     |                           | Mixed-Up Zombies          | Mixed-Up Zombies          | Mixed-Up Zombies         |
| 4 |Cafeteria or How Are You Going to Keep Her Down on the Farm after She’s Seen Paris Twice Unknown|96    |26        |52  |Going to Keep Her Down on|25   |NULL                   | Going to Keep Her Down on |                           |                           |                          |
| 4 |Cafeteria or How Are You Going to Keep Her Down on the Farm after She’s Seen Paris Twice Unknown|96    |26        |78  |the Farm after She’s Seen|25   |52                     |                           | the Farm after She’s Seen | the Farm after She’s Seen | the Farm after She’s Seen|
| 4 |Cafeteria or How Are You Going to Keep Her Down on the Farm after She’s Seen Paris Twice Unknown|96    |25        |26  |Cafeteria or How Are You |24   |78                     |                           |                           |                           |                          |
| 4 |Cafeteria or How Are You Going to Keep Her Down on the Farm after She’s Seen Paris Twice Unknown|96    |26        |104 |Paris Twice Unknown      |19   |26                     |                           | Paris Twice Unknown       | Paris Twice Unknown       | Paris Twice Unknown      |
| 4 |The Saga of the Viking Women and their Voyage to the Waters of the Great Sea Serpent Roger      |90    |26        |50  |Women and their Voyage to|25   |NULL                   | Women and their Voyage to |                           |                           |                          |
| 4 |The Saga of the Viking Women and their Voyage to the Waters of the Great Sea Serpent Roger      |90    |24        |74  |the Waters of the Great  |23   |50                     |                           | the Waters of the Great   | the Waters of the Great   | the Waters of the Great  |
| 4 |The Saga of the Viking Women and their Voyage to the Waters of the Great Sea Serpent Roger      |90    |23        |24  |The Saga of the Viking   |22   |74                     |                           |                           |                           |                          |
| 4 |The Saga of the Viking Women and their Voyage to the Waters of the Great Sea Serpent Roger      |90    |26        |100 |Sea Serpent Roger        |17   |24                     |                           | Sea Serpent Roger         | Sea Serpent Roger         | Sea Serpent Roger        |

| id | base_string_column                                                                               | fragment 1               | fragment 2                | fragment 3                | fragment 4            | 
| 15 | Cafeteria or How Are You Going to Keep Her Down on the Farm after She’s Seen Paris Twice Unknown | Cafeteria or How Are You | Going to Keep Her Down on | the Farm after She’s Seen | Paris Twice Unknown   |
| 4  | The Incredibly Creatures Who Stopped Living And Accidentally Became Mixed-Up Zombies             | The Incredibly Creatures | Who Stopped Living And    | Accidentally Became Mixed | Up Zombies            |
| 16 | The Saga of the Viking Women and their Voyage to the Waters of the Great Sea Serpent Roger       | The Saga of the Viking   | Women and their Voyage to | the Waters of the Great   | Sea Serpent Roger     |

步骤1：修改拆分逻辑，支持空格和连字符作为拆分符 #

核心是在查找拆分位置时，同时考虑空格和连字符，取指定长度范围内最靠近末尾的拆分符位置，避免拆分单词：

DECLARE @chars BIGINT = 25;

WITH parts AS (
    SELECT 
        id,
        base_string_column,
        LEN(base_string_column) AS length,
        CAST(1 AS BIGINT) AS next_pos,
        -- 生成第一个片段
        SUBSTRING(base_string_column, 1, 
            LEAST(
                ISNULL(NULLIF(CHARINDEX(' ', REVERSE(SUBSTRING(base_string_column + ' ', 1, @chars + 1))), 0), @chars + 1),
                ISNULL(NULLIF(CHARINDEX('-', REVERSE(SUBSTRING(base_string_column + ' ', 1, @chars + 1))), 0), @chars + 1)
            ) - 1
        ) AS fragment,
        CAST(1 AS INT) AS fragment_num
    FROM test

    UNION ALL

    SELECT 
        p.id,
        p.base_string_column,
        p.length,
        p.next_pos + LEN(p.fragment) + 1, -- 跳过拆分符
        -- 生成后续片段
        CASE 
            WHEN p.next_pos + LEN(p.fragment) + 1 + @chars > p.length THEN
                SUBSTRING(p.base_string_column, p.next_pos + LEN(p.fragment) + 1, p.length - (p.next_pos + LEN(p.fragment)))
            ELSE
                SUBSTRING(p.base_string_column, p.next_pos + LEN(p.fragment) + 1,
                    LEAST(
                        ISNULL(NULLIF(CHARINDEX(' ', REVERSE(SUBSTRING(p.base_string_column + ' ', p.next_pos + LEN(p.fragment) + 1, @chars + 1))), 0), @chars + 1),
                        ISNULL(NULLIF(CHARINDEX('-', REVERSE(SUBSTRING(p.base_string_column + ' ', p.next_pos + LEN(p.fragment) + 1, @chars + 1))), 0), @chars + 1)
                    ) - 1
                )
        END AS fragment,
        p.fragment_num + 1 AS fragment_num
    FROM parts p
    WHERE p.next_pos + LEN(p.fragment) <= p.length
)
-- 行列转换，将片段转为多列
SELECT 
    id,
    base_string_column,
    MAX(CASE WHEN fragment_num = 1 THEN fragment END) AS [fragment 1],
    MAX(CASE WHEN fragment_num = 2 THEN fragment END) AS [fragment 2],
    MAX(CASE WHEN fragment_num = 3 THEN fragment END) AS [fragment 3],
    MAX(CASE WHEN fragment_num = 4 THEN fragment END) AS [fragment 4]
FROM parts
GROUP BY id, base_string_column
ORDER BY id;

逻辑说明 #

1. 拆分符处理：在截取的目标长度子串中，通过反转字符串查找最近的空格或连字符，用LEAST函数取两者中更靠近末尾的位置作为拆分点，保证不拆分单词；
2. 片段编号：递归过程中为每个片段分配唯一编号fragment_num，用于后续行列转换；
3. 行列转换：使用MAX(CASE...)按片段编号分组，将行格式的拆分结果转换为目标多列格式。

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