You need to enable JavaScript to run this app. SQL如何按-分隔符将财务账户编码拆分为Seg01至Seg12多列
优惠活动
大模型
产品
解决方案
定价
更多

SQL如何按-分隔符将财务账户编码拆分为Seg01至Seg12多列

财务账户编码拆分SQL实现方案

假设存储财务编码的字段名为account_code,以下为不同主流数据库的实现代码,均支持按-拆分出12个分段(Seg01~Seg12),空分段返回空字符串:


核心逻辑

  • 连续-代表对应位置的分段为空值
  • 总分段不足12个时,尾部分段统一返回空值
  • 前4个分段固定存在,无需额外兼容判空

MySQL 实现

使用SUBSTRING_INDEX嵌套实现分段提取:

SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(account_code, '-', 1), '-', -1) AS Seg01,
  SUBSTRING_INDEX(SUBSTRING_INDEX(account_code, '-', 2), '-', -1) AS Seg02,
  SUBSTRING_INDEX(SUBSTRING_INDEX(account_code, '-', 3), '-', -1) AS Seg03,
  SUBSTRING_INDEX(SUBSTRING_INDEX(account_code, '-', 4), '-', -1) AS Seg04,
  SUBSTRING_INDEX(SUBSTRING_INDEX(account_code, '-', 5), '-', -1) AS Seg05,
  SUBSTRING_INDEX(SUBSTRING_INDEX(account_code, '-', 6), '-', -1) AS Seg06,
  SUBSTRING_INDEX(SUBSTRING_INDEX(account_code, '-', 7), '-', -1) AS Seg07,
  SUBSTRING_INDEX(SUBSTRING_INDEX(account_code, '-', 8), '-', -1) AS Seg08,
  SUBSTRING_INDEX(SUBSTRING_INDEX(account_code, '-', 9), '-', -1) AS Seg09,
  SUBSTRING_INDEX(SUBSTRING_INDEX(account_code, '-', 10), '-', -1) AS Seg10,
  SUBSTRING_INDEX(SUBSTRING_INDEX(account_code, '-', 11), '-', -1) AS Seg11,
  SUBSTRING_INDEX(SUBSTRING_INDEX(account_code, '-', 12), '-', -1) AS Seg12
FROM 你的表名;

PostgreSQL 实现

先将字符串转为数组,再按下标提取分段:

SELECT
  COALESCE((string_to_array(account_code, '-'))[1], '') AS Seg01,
  COALESCE((string_to_array(account_code, '-'))[2], '') AS Seg02,
  COALESCE((string_to_array(account_code, '-'))[3], '') AS Seg03,
  COALESCE((string_to_array(account_code, '-'))[4], '') AS Seg04,
  COALESCE((string_to_array(account_code, '-'))[5], '') AS Seg05,
  COALESCE((string_to_array(account_code, '-'))[6], '') AS Seg06,
  COALESCE((string_to_array(account_code, '-'))[7], '') AS Seg07,
  COALESCE((string_to_array(account_code, '-'))[8], '') AS Seg08,
  COALESCE((string_to_array(account_code, '-'))[9], '') AS Seg09,
  COALESCE((string_to_array(account_code, '-'))[10], '') AS Seg10,
  COALESCE((string_to_array(account_code, '-'))[11], '') AS Seg11,
  COALESCE((string_to_array(account_code, '-'))[12], '') AS Seg12
FROM 你的表名;

SQL Server 实现

先将编码字符串转为JSON数组,再按位置提取:

SELECT
  COALESCE(JSON_VALUE(CONCAT('["', REPLACE(account_code, '-', '","'), '"]'), '$[0]'), '') AS Seg01,
  COALESCE(JSON_VALUE(CONCAT('["', REPLACE(account_code, '-', '","'), '"]'), '$[1]'), '') AS Seg02,
  COALESCE(JSON_VALUE(CONCAT('["', REPLACE(account_code, '-', '","'), '"]'), '$[2]'), '') AS Seg03,
  COALESCE(JSON_VALUE(CONCAT('["', REPLACE(account_code, '-', '","'), '"]'), '$[3]'), '') AS Seg04,
  COALESCE(JSON_VALUE(CONCAT('["', REPLACE(account_code, '-', '","'), '"]'), '$[4]'), '') AS Seg05,
  COALESCE(JSON_VALUE(CONCAT('["', REPLACE(account_code, '-', '","'), '"]'), '$[5]'), '') AS Seg06,
  COALESCE(JSON_VALUE(CONCAT('["', REPLACE(account_code, '-', '","'), '"]'), '$[6]'), '') AS Seg07,
  COALESCE(JSON_VALUE(CONCAT('["', REPLACE(account_code, '-', '","'), '"]'), '$[7]'), '') AS Seg08,
  COALESCE(JSON_VALUE(CONCAT('["', REPLACE(account_code, '-', '","'), '"]'), '$[8]'), '') AS Seg09,
  COALESCE(JSON_VALUE(CONCAT('["', REPLACE(account_code, '-', '","'), '"]'), '$[9]'), '') AS Seg10,
  COALESCE(JSON_VALUE(CONCAT('["', REPLACE(account_code, '-', '","'), '"]'), '$[10]'), '') AS Seg11,
  COALESCE(JSON_VALUE(CONCAT('["', REPLACE(account_code, '-', '","'), '"]'), '$[11]'), '') AS Seg12
FROM 你的表名;

Oracle 实现

使用正则表达式直接提取对应位置的分段:

SELECT
  REGEXP_SUBSTR(account_code, '[^-]*', 1, 1) AS Seg01,
  REGEXP_SUBSTR(account_code, '[^-]*', 1, 2) AS Seg02,
  REGEXP_SUBSTR(account_code, '[^-]*', 1, 3) AS Seg03,
  REGEXP_SUBSTR(account_code, '[^-]*', 1, 4) AS Seg04,
  REGEXP_SUBSTR(account_code, '[^-]*', 1, 5) AS Seg05,
  REGEXP_SUBSTR(account_code, '[^-]*', 1, 6) AS Seg06,
  REGEXP_SUBSTR(account_code, '[^-]*', 1, 7) AS Seg07,
  REGEXP_SUBSTR(account_code, '[^-]*', 1, 8) AS Seg08,
  REGEXP_SUBSTR(account_code, '[^-]*', 1, 9) AS Seg09,
  REGEXP_SUBSTR(account_code, '[^-]*', 1, 10) AS Seg10,
  REGEXP_SUBSTR(account_code, '[^-]*', 1, 11) AS Seg11,
  REGEXP_SUBSTR(account_code, '[^-]*', 1, 12) AS Seg12
FROM 你的表名;

内容的提问来源于stack exchange,提问作者LordRofticus

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.16 05:36:08