Azure SQL数据库中按下划线拆分id列的技术问询

方法一：使用CHARINDEX + SUBSTRING（推荐，适配单下划线固定格式） #

你的id列格式统一为前缀_数字，每个值仅含一个下划线，直接通过字符定位+截取即可完成拆分：

SELECT
  SUBSTRING(id, 1, CHARINDEX('_', id) - 1) AS id1,
  SUBSTRING(id, CHARINDEX('_', id) + 1, LEN(id) - CHARINDEX('_', id)) AS id2,
  -- 保留原表其他字段
  *
FROM
  你的表名;

关键逻辑说明： #

• CHARINDEX('_', id)：返回下划线在当前id字符串中的位置
• SUBSTRING(id, 1, 位置-1)：截取下划线之前的内容作为id1
• SUBSTRING(id, 位置+1, 剩余长度)：截取下划线之后的内容作为id2

如果需要永久修改表结构（新增id1、id2列并填充数据），可执行以下两步：

1. 添加新列：

ALTER TABLE 你的表名
ADD id1 VARCHAR(50), id2 VARCHAR(50); -- 根据实际数据长度调整类型参数

2. 批量填充数据：

UPDATE 你的表名
SET
  id1 = SUBSTRING(id, 1, CHARINDEX('_', id) - 1),
  id2 = SUBSTRING(id, CHARINDEX('_', id) + 1, LEN(id) - CHARINDEX('_', id));

方法二：使用STRING_SPLIT + CROSS APPLY（适配多下划线场景，需Azure SQL DB 2016及以上版本） #

若未来id可能出现多个下划线，或需要更灵活的拆分规则，可通过拆分转行再转列的方式实现：

SELECT
  MAX(CASE WHEN rn = 1 THEN value END) AS id1,
  MAX(CASE WHEN rn = 2 THEN value END) AS id2,
  -- 保留原表其他字段
  t.*
FROM
  你的表名 t
CROSS APPLY (
  SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
  FROM STRING_SPLIT(t.id, '_')
) AS split
GROUP BY
  t.id, t.其他字段名; -- 替换为原表主键或所有非拆分字段

关键逻辑说明： #

• STRING_SPLIT(t.id, '_')：将每行id按下划线拆分为多行记录
• ROW_NUMBER()：为拆分后的每行标记序号，区分前缀（rn=1）和后缀（rn=2）
• MAX(CASE...)：将拆分后的行数据转换为列，得到id1和id2

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