如何在列中选择值 [英] How to select values within a column
问题描述
道歉，只分享很少的信息.应该提供了整个东西.就是这样.
我们正在通过工具将来自许多数据库的预检查补丁信息发送到另一台服务器上的某些日志，该服务器具有主数据库，该主数据库将存储此信息以供以后进行比较.我们正在使用标签<>"来使我们的声明唯一以进行识别.
日志文件信息如下:
StepLog:Info:dbname instance_name主机名objnames 等等.
如何从该服务器上的数据库中提取标签之间的值以存储/插入表中，并跳过标签.由于该信息将来自多个数据库，因此无法硬引用该信息.
谢谢.
基于带有 regexp split 的解决方案
a>您可以运行以下查询.
但是请注意，您必须事先知道结果列的最大数量.
with t1 as (select 1 rn, 'Orcl, orcl, linux box, Pass, tablespace_name1, tablespace_name2' col from dual union all
select 2 rn, 'Orcl2, orcl2, linux box2, Pass2, tablespace_name12, tablespace_name22' col from dual),
t2 as (select rownum colnum from dual connect by level <= 6 /* (max) number of columns */)
select t1.rn, t2.colnum, rtrim(ltrim(regexp_substr(t1.col,'[^,]+', 1, t2.colnum))) col from t1, t2
where regexp_substr(t1.col, '[^,]+', 1, t2.colnum) is not null
order by rn,colnum;
这将为您提供行，列和键视图:
RN COLNUM COL
----- ---------- ------------------
1 1 Orcl
1 2 orcl
1 3 linux box
1 4 Pass
1 5 tablespace_name1
1 6 tablespace_name2
2 1 Orcl2
2 2 orcl2
2 3 linux box2
2 4 Pass2
2 5 tablespace_name12
2 6 tablespace_name22
您可以使用PIVOT查询在平面表中获取结果
with t1 as (select 1 rn, 'Orcl, orcl, linux box, Pass, tablespace_name1, tablespace_name2' col from dual union all
select 2 rn, 'Orcl2, orcl2, linux box2, Pass2, tablespace_name12, tablespace_name22' col from dual),
t2 as (select rownum colnum from dual connect by level <= 6 /* (max) number of columns */),
t3 as (select t1.rn, t2.colnum, rtrim(ltrim(regexp_substr(t1.col,'[^,]+', 1, t2.colnum))) col from t1, t2
where regexp_substr(t1.col, '[^,]+', 1, t2.colnum) is not null)
select * from t3
PIVOT (max(col) col for (colnum) in
(1 as "1",
2 as "2",
3 as "3",
4 as "4",
5 as "5",
6 as "6"))
order by rn;
RN 1_COL 2_COL 3_COL 4_COL 5_COL 6_COL
----- --------- --------- --------- --------- --------- ---------
1 Orcl orcl linux box Pass tablespace_name1 tablespace_name2
2 Orcl2 orcl2 linux box2Pass2 tablespace_name12 tablespace_name22
同样，如您所见，您必须在查询中列出所有结果列.
Apologies for sharing little information than needed. Should have provided the entire thing. Here's it.
we are sending pre-check patching information from many DB's via a tool to some logs on another server, that server has the main DB which will store this info for comparing later on. We are using the tags "<>" to make our statements unique for identification.
The logfile info will look like this:
StepLog:Info: dbname instance_name hostname objnames and so on..
From the database on that server, how do I extract only the values between the tags to store/insert in a table, and skip the tags. Since the info will me from multiple DB's cannot hard quote info.
Thanks.
Based on the solution with regexp split you may run following query.
Note however, that you must know the maximal number of the resulting columns in advance.
with t1 as (select 1 rn, 'Orcl, orcl, linux box, Pass, tablespace_name1, tablespace_name2' col from dual union all
select 2 rn, 'Orcl2, orcl2, linux box2, Pass2, tablespace_name12, tablespace_name22' col from dual),
t2 as (select rownum colnum from dual connect by level <= 6 /* (max) number of columns */)
select t1.rn, t2.colnum, rtrim(ltrim(regexp_substr(t1.col,'[^,]+', 1, t2.colnum))) col from t1, t2
where regexp_substr(t1.col, '[^,]+', 1, t2.colnum) is not null
order by rn,colnum;
This gives you the row, column and key view:
RN COLNUM COL
----- ---------- ------------------
1 1 Orcl
1 2 orcl
1 3 linux box
1 4 Pass
1 5 tablespace_name1
1 6 tablespace_name2
2 1 Orcl2
2 2 orcl2
2 3 linux box2
2 4 Pass2
2 5 tablespace_name12
2 6 tablespace_name22
You may use the PIVOT query to get the result in flat table
with t1 as (select 1 rn, 'Orcl, orcl, linux box, Pass, tablespace_name1, tablespace_name2' col from dual union all
select 2 rn, 'Orcl2, orcl2, linux box2, Pass2, tablespace_name12, tablespace_name22' col from dual),
t2 as (select rownum colnum from dual connect by level <= 6 /* (max) number of columns */),
t3 as (select t1.rn, t2.colnum, rtrim(ltrim(regexp_substr(t1.col,'[^,]+', 1, t2.colnum))) col from t1, t2
where regexp_substr(t1.col, '[^,]+', 1, t2.colnum) is not null)
select * from t3
PIVOT (max(col) col for (colnum) in
(1 as "1",
2 as "2",
3 as "3",
4 as "4",
5 as "5",
6 as "6"))
order by rn;
RN 1_COL 2_COL 3_COL 4_COL 5_COL 6_COL
----- --------- --------- --------- --------- --------- ---------
1 Orcl orcl linux box Pass tablespace_name1 tablespace_name2
2 Orcl2 orcl2 linux box2Pass2 tablespace_name12 tablespace_name22
Again, as you see you must list all the resulting columns in the query.
这篇关于如何在列中选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
