Scala实现含内部逗号的SELECT SQL列拆分方案问询

问题场景 #

当处理包含复杂表达式（如带内部逗号的CASE、coalesce等函数）的SQL SELECT语句时，直接用逗号拆分SELECT子句会误将表达式内部的逗号识别为列分隔符，导致拆分结果错误。

示例SQL语句：

select id, name as emp_name, case when coalesce(sal,0)=0 then 0 else 1 end as emp_sal from employee;

当前错误输出 #

"id"
"name as emp_name"
"case when coalesce(sal"
"0)=0 then 0 else 1 end as emp_sal"

预期正确输出 #

"id"
"name as emp_name"
"case when coalesce(sal,0)=0 then 0 else 1 end as emp_sal"

现有错误代码 #

// Your SQL query
val sqlQuery = "select id, name as emp_name, case when coalesce(sal,0)=0 then 0 else 1 end as emp_sal from employee"

// Extract the part between "select" and "from"
val selectPart = sqlQuery.split("(?i)select ")(1).split("(?i) from")(0).trim

// Split the select part into individual columns while preserving complex expressions
val columns = selectPart.split(",").map(_.trim)

// Iterate over the columns and print them
for (column <- columns) {
println(s""""$column"""")
}

正确实现方案 #

核心思路是通过跟踪括号嵌套深度来判断逗号是否为列分隔符：只有当逗号不在任何括号内部时，才作为拆分标记。以下是Scala实现代码：

val sqlQuery = "select id, name as emp_name, case when coalesce(sal,0)=0 then 0 else 1 end as emp_sal from employee"

// 提取select和from之间的部分
val selectPart = sqlQuery.split("(?i)select ")(1).split("(?i) from")(0).trim

// 按顶层逗号拆分（忽略括号内的逗号）
def splitOnTopLevelCommas(s: String): List[String] = {
  var bracketDepth = 0
  val currentSegment = new StringBuilder()
  val result = scala.collection.mutable.ListBuffer[String]()
  
  for (char <- s) {
    char match {
      case '(' =>
        bracketDepth += 1
        currentSegment.append(char)
      case ')' =>
        bracketDepth = math.max(0, bracketDepth - 1)
        currentSegment.append(char)
      case ',' if bracketDepth == 0 =>
        result.append(currentSegment.toString.trim)
        currentSegment.clear()
      case _ =>
        currentSegment.append(char)
    }
  }
  
  // 添加最后一段内容
  if (currentSegment.nonEmpty) {
    result.append(currentSegment.toString.trim)
  }
  
  result.toList
}

val columns = splitOnTopLevelCommas(selectPart)

// 输出结果
for (column <- columns) {
  println(s""""$column"""")
}

代码逻辑说明 #

1. 遍历SELECT子句的每个字符，维护bracketDepth变量记录当前括号嵌套层级：遇到左括号层级+1，右括号层级-1（最小为0）。
2. 当遇到逗号且bracketDepth为0时，说明这是列之间的分隔符，将当前累积的字符串作为一个列项存入结果，清空当前缓存。
3. 其他字符直接追加到当前缓存中，遍历结束后将最后一段缓存内容加入结果。

这样就能正确保留CASE、嵌套函数等复杂表达式的完整性，避免误拆分。

内容的提问来源于stack exchange，提问作者Raghavendra Reddy ToLearn