无分隔符平面文件导入SQL时如何选择列范围并提取指定字段

你提供的数据属于典型的空格分隔+固定字段规则的平面文件，可按以下步骤拆分提取所需字段并加载到目标表：

一、先确认拆分规则 #

从你给出的示例：

"700133 70013303102021SC  25 210604580000000440"
"700133 70013303102021SC  26 210604600000010000"

目标roll number是最后一个字段去掉固定前缀后的内容：最后1个字段整体为210604+2位标识+roll number的结构，只需按规则截取对应位置即可。

二、两种拆分实现方案 #

方案1：按空格分隔提取（适合字段间用空格做分隔的场景） #

字段间空格数量不影响拆分效果，适配性更强：

• 大批量数据可先预处理：Linux环境下用sed命令把连续空格替换为单种分隔符，生成标准CSV文件再导入，命令示例：
  sed 's/ */,/g' 原始文件路径 > 预处理后文件路径
• 导入后SQL拆分（以MySQL为例）：先把整行数据导入临时表的raw_line字段，再按规则拆分：

  -- 提取roll number示例，截取起始位可根据你的实际规则调整
  SELECT 
    -- 提取最后一个字段并去掉首尾引号
    TRIM('"' FROM SUBSTRING_INDEX(raw_line, ' ', -1)) AS last_field,
    -- 从最后一个字段的第8位开始截取到末尾，得到roll number
    SUBSTR(TRIM('"' FROM SUBSTRING_INDEX(raw_line, ' ', -1)), 8) AS roll_number
  FROM 临时表名;
  

方案2：按固定位置截取（适合所有行字段宽度完全固定的场景） #

如果每一行每个字段的字符长度完全固定，直接按位置截取更稳定，不会受空格异常的影响：

-- Oracle示例，其他数据库语法类似，仅需调整字符串函数名称
SELECT 
  -- 先定位最后一个空格的位置，再提取后面的内容去掉引号
  TRIM('"' FROM SUBSTR(raw_line, INSTR(raw_line, ' ', -1) + 1)) AS last_field,
  -- 截取对应位置得到roll number
  SUBSTR(TRIM('"' FROM SUBSTR(raw_line, INSTR(raw_line, ' ', -1) + 1)), 8) AS roll_number
FROM 临时表名;

三、加载到已创建目标表的步骤 #

1. 先创建临时表存储原始行数据：
   CREATE TEMPORARY TABLE temp_raw (raw_line VARCHAR(200) NOT NULL);
2. 用对应数据库的批量导入工具把平面文件导入临时表：MySQL用LOAD DATA INFILE、PostgreSQL用COPY、SQL Server用BULK INSERT均可
3. 从临时表提取所有字段插入到目标表：

   INSERT INTO 目标表名 (字段1, 字段2, 字段3, roll_number)
   SELECT 
     TRIM('"' FROM SUBSTRING_INDEX(raw_line, ' ', 1)) AS 字段1,
     SUBSTRING_INDEX(SUBSTRING_INDEX(raw_line, ' ', 2), ' ', -1) AS 字段2,
     TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(raw_line, ' ', 3), ' ', -1)) AS 字段3,
     SUBSTR(TRIM('"' FROM SUBSTRING_INDEX(raw_line, ' ', -1)), 8) AS roll_number
   FROM temp_raw;
   

4. 验证目标表数据无误后删除临时表即可。

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