Spark SQL拆分字符串列时如何让无分隔符行的首字段返回null

以下是不同SQL方言的可运行实现，均能满足无分隔符时col1返回null、col2返回原值的需求：

通用逻辑（兼容多数SQL引擎） #

核心思路：判断原字符串是否包含分隔符-，分别处理两种场景，避免拆分后数组长度不一致的问题。
示例使用原列名为original_col、表名为your_table，可自行替换为实际名称。

Hive/Spark SQL 写法 #

写法1：无需子查询，直接判断

SELECT
  CASE WHEN original_col RLIKE '-' THEN split(original_col, '-', 2)[0] END AS col1,
  COALESCE(split(original_col, '-', 2)[1], original_col) AS col2
FROM your_table;

写法2：仅拆分一次，性能更优

SELECT
  IF(size(split_arr) = 2, split_arr[0], NULL) AS col1,
  IF(size(split_arr) = 2, split_arr[1], split_arr[0]) AS col2
FROM (
  SELECT split(original_col, '-', 2) AS split_arr FROM your_table
) t;

Spark SQL 2.4+ 还可以用element_at简化写法：

SELECT
  element_at(split(original_col, '-', 2), 1) AS col1,
  COALESCE(element_at(split(original_col, '-', 2), 2), original_col) AS col2
FROM your_table;

MySQL 8.0+ 写法 #

SELECT
  IF(LOCATE('-', original_col) > 0, SUBSTRING_INDEX(original_col, '-', 1), NULL) AS col1,
  SUBSTRING_INDEX(original_col, '-', -1) AS col2
FROM your_table;

PostgreSQL 写法 #

SELECT
  CASE WHEN strpos(original_col, '-') > 0 THEN split_part(original_col, '-', 1) END AS col1,
  split_part(original_col, '-', CASE WHEN strpos(original_col, '-') > 0 THEN 2 ELSE 1 END) AS col2
FROM your_table;

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