I have a column which contains a pair key-value:
我有一个包含一对键值的列：
key1=111111|key2=222222|key3=333333|key4=444444
键1 = 111111 |键2 = 222222 | KEY3 = 333333 | KEY4 = 444444
value is id of the column in different table. How can I get value '222222' if key2 exists and then use it to join with another table?
value是不同表中列的id。如果key2存在，我怎样才能得到值'222222'然后用它来加入另一个表？
Could you provide rexeg for me? Thanks in advance.
你能为我提供rexeg吗？提前致谢。
1 个解决方案
#1
1
If this was instead in "proper form" (if instead of one value in one column you would have four rows in two columns - a table with a column for key and another for value) then it would be easy, right? And - if your column is paired to another column, an id of some sort, then the same column should be in the "proper form" table?
如果这是“正确的形式”（如果在一列中不是一个值，而是在两列中有四行 - 一个表中有一列用于键而另一列用于值）那么这很容易，对吧？并且 - 如果您的列与另一列（某种类型的id）配对，那么同一列应该在“正确的形式”表中？
I don't ever understand how people end up having to use something like "your" existing column. But if you have no power over it, it makes the most sense to write a view (perhaps a materialized one for better performance), and use it instead of your current table. Something like:
我不明白人们最终会如何使用“你的”现有专栏。但是如果你对它没有任何影响，最有意义的是编写一个视图（可能是一个物化的视图以获得更好的性能），并使用它而不是当前的表。就像是：
create table t1 ( id number, key_val varchar2(4000) );
insert into t1
select 101, 'key1=111111|key2=222222|key3=333333|key4=444444' from dual union all
select 102, 'key1=800001|key5=800002|key6=900030|key9=339900' from dual;
commit;
create view t1_view ( id, key, val ) as (
select id, regexp_substr(key_val, '(\||^)(.*?)=', 1, level, null, 2),
regexp_substr(key_val, '=(.*?)(\||$)', 1, level, null, 1)
from t1
connect by level <= length(key_val) - length(translate(key_val, 'z=', 'z'))
and prior id = id
and prior sys_guid() is not null
);
select * from t1_view;
ID KEY VAL
---- ----- -------
101 key1 111111
101 key2 222222
101 key3 333333
101 key4 444444
102 key1 800001
102 key5 800002
102 key6 900030
102 key9 339900
8 rows selected.
Here val is still a string. If it is supposed to be a number, it is best to wrap the last regexp_substr within to_number().
这里val仍然是一个字符串。如果它应该是一个数字，最好将最后一个regexp_substr包装在to_number（）中。
And now write all your joins to use this view instead of t1.
现在编写所有联接以使用此视图而不是t1。
#1
1
If this was instead in "proper form" (if instead of one value in one column you would have four rows in two columns - a table with a column for key and another for value) then it would be easy, right? And - if your column is paired to another column, an id of some sort, then the same column should be in the "proper form" table?
如果这是“正确的形式”（如果在一列中不是一个值，而是在两列中有四行 - 一个表中有一列用于键而另一列用于值）那么这很容易，对吧？并且 - 如果您的列与另一列（某种类型的id）配对，那么同一列应该在“正确的形式”表中？
I don't ever understand how people end up having to use something like "your" existing column. But if you have no power over it, it makes the most sense to write a view (perhaps a materialized one for better performance), and use it instead of your current table. Something like:
我不明白人们最终会如何使用“你的”现有专栏。但是如果你对它没有任何影响，最有意义的是编写一个视图（可能是一个物化的视图以获得更好的性能），并使用它而不是当前的表。就像是：
create table t1 ( id number, key_val varchar2(4000) );
insert into t1
select 101, 'key1=111111|key2=222222|key3=333333|key4=444444' from dual union all
select 102, 'key1=800001|key5=800002|key6=900030|key9=339900' from dual;
commit;
create view t1_view ( id, key, val ) as (
select id, regexp_substr(key_val, '(\||^)(.*?)=', 1, level, null, 2),
regexp_substr(key_val, '=(.*?)(\||$)', 1, level, null, 1)
from t1
connect by level <= length(key_val) - length(translate(key_val, 'z=', 'z'))
and prior id = id
and prior sys_guid() is not null
);
select * from t1_view;
ID KEY VAL
---- ----- -------
101 key1 111111
101 key2 222222
101 key3 333333
101 key4 444444
102 key1 800001
102 key5 800002
102 key6 900030
102 key9 339900
8 rows selected.
Here val is still a string. If it is supposed to be a number, it is best to wrap the last regexp_substr within to_number().
这里val仍然是一个字符串。如果它应该是一个数字，最好将最后一个regexp_substr包装在to_number（）中。
And now write all your joins to use this view instead of t1.
现在编写所有联接以使用此视图而不是t1。