时间戳

首页 > 解决方案 > 在Oracle中将连字符分隔的字符串拆分为行

sql - 在Oracle中将连字符分隔的字符串拆分为行

问题描述

我在表中有一个列，它按顺序存储数据。一些数据使用连字符分隔，一些数据使用逗号分隔。我想将数据拆分成行。问题是逗号分隔的值在每个逗号之后被视为单个值，但对于连字符，它意味着一种数据范围。例如，如果字符串是这样的'A1, A2, A4'，则表示有 3 个值，将被转换为 3 行。还有类似字符串'A1-A4'，表示有 4 个值，将转换为 4 行，因为连字符表示值的范围，表示起始值和结束值。

我能够转换逗号分隔的值，但不确定如何在 oracle 中拆分连字符分隔的范围。

  SELECT regexp_substr('A1,A2,A4' , '[^,]+', 1, level) as a
  FROM dual
  CONNECT BY regexp_substr('A1,A2,A4', '[^,]+', 1, level) is not null

以上 ddl 将提供的字符串转换为 3 行，这很好。

  SELECT regexp_substr('A1-A4' , '[^-]+', 1, level) as a
  FROM dual
  CONNECT BY regexp_substr('A1-A4', '[^-]+', 1, level) is not null

但是上面的查询应该返回 4 行，但我不知道如何实现这一点。有什么想法吗 ？

标签： sqloraclesplit

解决方案

假设模式始终是一对具有相同前缀（此处为“A”）且每个值后跟一个数字的值，您可以使用不同的正则表达式来提取前缀、起始数字和结束数字：

SELECT
  regexp_substr('A1-A4' , '(.*?)(\d+)-.*?(\d+)', 1, 1, null, 1) as prefix,
  to_number(regexp_substr('A1-A4' , '(.*?)(\d+)-.*?(\d+)', 1, 1, null, 2)) as start_num,
  to_number(regexp_substr('A1-A4' , '(.*?)(\d+)-.*?(\d+)', 1, 1, null, 3)) as end_num
FROM dual

PREFIX  START_NUM    END_NUM
------  ---------  ---------
A               1          4

然后在递归 CTE 中使用它来获取两者之间的值：

WITH rcte (prefix, num, end_num) AS (
  SELECT
    regexp_substr('A1-A4' , '(.*?)(\d+)-.*?(\d+)', 1, 1, null, 1),
    to_number(regexp_substr('A1-A4' , '(.*?)(\d+)-.*?(\d+)', 1, 1, null, 2)),
    to_number(regexp_substr('A1-A4' , '(.*?)(\d+)-.*?(\d+)', 1, 1, null, 3))
  FROM dual
  UNION ALL
  SELECT prefix, num + 1, end_num
  FROM rcte
  WHERE num < end_num
)
SELECT prefix || num as result
FROM rcte

RESULT
------
A1
A2
A3
A4

db<>小提琴

您可以在一个查询中结合这两种方法，进一步假设您没有在同一字符串中混合使用逗号分隔的值和范围；db<>小提琴演示。如果您确实有混合，则可以将它们串联应用；将逗号分隔的行转换为行，然后进一步处理任何实际上是连字符范围的新行。

推荐阅读