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
相关产品推荐
相关产品推荐

