在Oracle SQL中选择定界字符串作为表 [英] Selecting delimited string as a table in Oracle sql

问题描述

我有一个像这样的字符串:

I have a string like:

宽度:10 | 7 | 20 | 45，高度:25 | 5 | 6 | 45，长度:35 | 6 | 3 | 4"

"Width:10|7|20|45,Height:25|5|6|45,Length:35|6|3|4"

我正在寻找一个选择查询，以将其选择为表格，例如:

I'm looking to write a select query to select this as a table like:

Width | Height | Length
-----------------------
10    |  25    |   35  
7     |  5     |   6   
20    |  6     |   3  
45    |  45    |   4  

如果需要更多信息，请发表评论.

Please comment if you need any more information.

推荐答案

此解决方案适用于任意数量的列(宽度，高度，...)和值.

This solution works with an arbitrary number of columns (width, height, ...) and values.

-- your test data  
with data(val) as
 (select 'Width:10|7|20|45,Height:25|5|6|45,Length:35|6|3|4' from dual),

-- split by ,
cols as
 (select regexp_substr(str, '[^,]+', 1, level) val
    from (select val as str from data)
  connect by regexp_substr((select val as str from data),
                           '[^,]+',
                           1,
                           level) is not null),

-- split by :
hdr_and_cols as
 (select substr(val, 1, instr(val, ':') - 1) as hdr,
         substr(val, instr(val, ':') + 1) as val
    from cols),

-- split by |
hdr_lvl_vals as
 (select distinct x.hdr,
                  level as entry,
                  regexp_substr(x.val, '[^|]+', 1, level) as val
    from hdr_and_cols x
  connect by regexp_substr(x.val, '[^|]+', 1, level) is not null)

select * from hdr_lvl_vals;

结果:

hdr     entry   value
---------------------
Height  1       25
Height  2       5
Height  3       6
Height  4       45
Length  1       35
Length  2       6
Length  3       3
Length  4       4
Width   1       10
Width   2       7
Width   3       20
Width   4       45

您可以按照自己喜欢的方式格式化结果

You can format the result in the way you like e.g.

-- your test data  
with data(val) as
 (select 'Width:10|7|20|45,Height:25|5|6|45,Length:35|6|3|4' from dual),

-- split by ,
cols as
 (select regexp_substr(str, '[^,]+', 1, level) val
    from (select val as str from data)
  connect by regexp_substr((select val as str from data),
                           '[^,]+',
                           1,
                           level) is not null),

-- split by :
hdr_and_cols as
 (select substr(val, 1, instr(val, ':') - 1) as hdr,
         substr(val, instr(val, ':') + 1) as val
    from cols),

-- split by |
hdr_lvl_vals as
 (select distinct x.hdr,
                  level as entry,
                  regexp_substr(x.val, '[^|]+', 1, level) as val
    from hdr_and_cols x
  connect by regexp_substr(x.val, '[^|]+', 1, level) is not null)

-- format output
select w.val as width, h.val as heigth, l.val as length
  from (select entry, val from hdr_lvl_vals where hdr = 'Width') w,
       (select entry, val from hdr_lvl_vals where hdr = 'Height') h,
       (select entry, val from hdr_lvl_vals where hdr = 'Length') l,
       (select level as entry
          from dual
        connect by level <= (select max(entry) from hdr_lvl_vals)) r
 where r.entry = w.entry
   and r.entry = h.entry
   and r.entry = l.entry;

输出:

WIDTH   HEIGTH  LENGTH
--------------------
10      25      35
7       5       6
20      6       3
45      45      4

这篇关于在Oracle SQL中选择定界字符串作为表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！