如何在Databricks中使用SQL拆分特定格式字符串为结构化数据？

核心思路 #

先将单行字符串按行分隔符<RET>拆分为独立行记录，过滤掉表头行后，再按字段分隔符<TAB>拆分每行，映射到对应列。

分数据库实现示例 #

假设目标表为material_table，存储原始字符串的列名为raw_data，原始字符串格式如下：

物料编码<TAB>物料名称<TAB>规格型号<RET>001<TAB>螺丝<TAB>M3<RET>002<TAB>螺母<TAB>M3

PostgreSQL

利用string_to_array+unnest拆分行，split_part拆分列：

WITH split_rows AS (
    SELECT unnest(string_to_array(raw_data, '<RET>')) AS row_data
    FROM material_table
),
filtered_rows AS (
    SELECT row_data
    FROM split_rows
    -- 过滤表头行，或用行序号排除第一行（如果拆分后第一行是表头）
    WHERE row_data NOT LIKE '物料编码<TAB>%'
)
SELECT
    split_part(row_data, '<TAB>', 1) AS 物料编码,
    split_part(row_data, '<TAB>', 2) AS 物料名称,
    split_part(row_data, '<TAB>', 3) AS 规格型号
FROM filtered_rows;

MySQL 8.0+

用递归CTE拆分行，SUBSTRING_INDEX拆分列：

WITH RECURSIVE split_rows AS (
    SELECT 
        raw_data,
        SUBSTRING_INDEX(raw_data, '<RET>', 1) AS row_data,
        SUBSTRING(raw_data, LENGTH(SUBSTRING_INDEX(raw_data, '<RET>', 1)) + LENGTH('<RET>') + 1) AS remaining_data
    FROM material_table
    UNION ALL
    SELECT 
        raw_data,
        SUBSTRING_INDEX(remaining_data, '<RET>', 1),
        SUBSTRING(remaining_data, LENGTH(SUBSTRING_INDEX(remaining_data, '<RET>', 1)) + LENGTH('<RET>') + 1)
    FROM split_rows
    WHERE remaining_data != ''
),
filtered_rows AS (
    SELECT row_data
    FROM split_rows
    WHERE row_data NOT LIKE '物料编码<TAB>%'
)
SELECT
    SUBSTRING_INDEX(row_data, '<TAB>', 1) AS 物料编码,
    SUBSTRING_INDEX(SUBSTRING_INDEX(row_data, '<TAB>', 2), '<TAB>', -1) AS 物料名称,
    SUBSTRING_INDEX(row_data, '<TAB>', -1) AS 规格型号
FROM filtered_rows;

SQL Server 2022+

借助STRING_SPLIT的序号参数保留行顺序，用PARSENAME拆分列（适用于列数≤4的场景）：

WITH split_rows AS (
    SELECT value AS row_data
    FROM material_table
    CROSS APPLY STRING_SPLIT(raw_data, '<RET>', 1) -- 1表示返回行序号，确保顺序
),
filtered_rows AS (
    SELECT row_data
    FROM split_rows
    WHERE row_data NOT LIKE '物料编码<TAB>%'
)
SELECT
    PARSENAME(REPLACE(row_data, '<TAB>', '.'), 3) AS 物料编码,
    PARSENAME(REPLACE(row_data, '<TAB>', '.'), 2) AS 物料名称,
    PARSENAME(REPLACE(row_data, '<TAB>', '.'), 1) AS 规格型号
FROM filtered_rows;

关键注意事项 #

• 若实际存储的分隔符是转义字符（如<TAB>对应CHAR(9)，<RET>对应CHAR(10)），需将SQL中的<TAB>和<RET>替换为对应字符
• 若表头行无法通过内容匹配过滤，可通过拆分后的行序号排除第一行
• 列数不固定时，需改用动态SQL生成列映射逻辑

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