分隔符拆分多列SQL

分隔符拆分多列SQL

--- 首先查看最多的列是多少
SELECT   KM_JC ,  (LENGTH(REMARK) - LENGTH(REPLACE(REMARK, '-', '')))   FROM  KMYE_ERP_DJZ   ORDER BY (LENGTH(REMARK) - LENGTH(REPLACE(REMARK, '-', '')))  desc   ;


--- 根据最多的列数 , 构建分隔后的多列
SELECT 
  KM_JC as KM_JC,
  REGEXP_SUBSTR(text, '[^-]+', 1, 1) AS field1,
  REGEXP_SUBSTR(text, '[^-]+', 1, 2) AS field2,
  REGEXP_SUBSTR(text, '[^-]+', 1, 3) AS field3,
  REGEXP_SUBSTR(text, '[^-]+', 1, 4) AS field4,
  REGEXP_SUBSTR(text, '[^-]+', 1, 5) AS field5,
  REGEXP_SUBSTR(text, '[^-]+', 1, 6) AS field6
FROM (
   SELECT   KM_JC , REMARK as text FROM  KMYE_ERP_DJZ   ORDER BY KM_JC ASC     
);

示例截图如下: