sql - 在Oracle中将字符串拆分为多行，用管道分隔

Question

我在 Oracle 中有 3 列的数据，如下所示：

username    user.prof    values
__________________________________________
user_name1       user.prof1       1|2|3
user_name2       user.prof2       NOT_REQUIRED
user_name3       user.prof3       779|678|1|2|3

如您所见，这些值由竖线字符分隔，并且它们的编号不固定。期望的结果：

username    user.prof    values
__________________________________________
user_name1    user.prof1    1
user_name1    user.prof1    2
user_name1    user.prof1    3
user_name2    user.prof2    NOT_REQUIRED
user_name3    user.prof3    779
user_name3    user.prof3    678
user_name3    user.prof3    1
user_name3    user.prof3    2
user_name3    user.prof3    3

有人可以帮我弄这个吗？谢谢！！

标签： sqloraclesplitpipedelimiter

Answer 1

这是一个选项：

SQL> with test (username, user_prof, value) as
  2    (select 'user_name1', 'user.prof1', '1|2|3'         from dual union all
  3     select 'user_name2', 'user.prof2', 'NOT_REQUIRED'  from dual union all
  4     select 'user_name3', 'user.prof3', '779|678|1|2|3' from dual
  5    )
  6  select
  7    username,
  8    user_prof,
  9    regexp_substr(value, '[^\|]+', 1, column_value) value
 10  from test cross join
 11       table(cast(multiset(select level from dual
 12                           connect by level <= regexp_count(value, '\|') + 1
 13                          ) as sys.odcinumberlist));

USERNAME   USER_PROF  VALUE
---------- ---------- ----------------------------------------------------
user_name1 user.prof1 1
user_name1 user.prof1 2
user_name1 user.prof1 3
user_name2 user.prof2 NOT_REQUIRED
user_name3 user.prof3 779
user_name3 user.prof3 678
user_name3 user.prof3 1
user_name3 user.prof3 2
user_name3 user.prof3 3

9 rows selected.

SQL>