用查询中的值替换字段的一部分 [英] Replace part of field with values from query
问题描述
我有一个字段,其中填充了一系列特定格式的条件:
I have a field that is populated with a series of criteria in a specific format:
CRITERIA
$1 = True
$2 > $3
标准信息存储在表中
CRIT_ID CRIT_DESCRIPTION
1 Example 1
2 Example 2
3 Example 3
最终输出应如下所示
CRITERIA
Example 1 = True
Example 2 > Example 3
有人可以建议如何最好地做到这一点吗?到目前为止,我已经尝试过REPLACE并涉足REGEXP_REPLACE ...
Can anyone suggest how best to accomplish this? I have tried REPLACE and dabbled with REGEXP_REPLACE so far...
推荐答案
也许不是最有效的方法,但它是递归工作的(也就是说,如果crit_description
本身包含占位符",那么它们也会被扩展.(A第一个解决方案比下面显示的简单,没有执行此递归步骤.)请参阅我添加的第三个示例输入.如果可以进行更多清理,我将在稍后再次发布.
Perhaps not the most efficient, but it works recursively (that is, if the crit_description
itself contains "placeholders", those are expanded as well. (A first solution, simpler than what's shown below, didn't do this recursive step.) See the third sample input which I added. I will post again later if I can clean it up some more.
注意:这假定在criteria_info
表中实际找到了所有占位符";我没有测试如果找不到它们会发生什么. OP陈述要求.
NOTE: This assumes all "placeholders" are actually found in the criteria_info
table; I didn't test what happens if they are not found. OP to state the requirement.
with
inputs ( criteria ) as (
select '$1 = True' from dual union all
select '$2 > $3' from dual union all
select '$1 = $4' from dual
),
criteria_info ( crit_id, crit_description ) as (
select 1, 'Example 1' from dual union all
select 2, 'Example 2' from dual union all
select 3, 'Example 3' from dual union all
select 4, '$2 + $3' from dual
),
rec ( criteria, new_str ) as (
select criteria, criteria
from inputs
union all
select r.criteria,
regexp_replace(r.new_str, '\$\d+', c.crit_description, 1, 1)
from rec r inner join criteria_info c
on to_number(regexp_substr(r.new_str, '\$(\d+)', 1, 1, null, 1)) = c.crit_id
where regexp_substr(r.new_str, '\$\d+') is not null
)
select criteria, new_str
from rec
where regexp_substr(new_str, '\$\d+') is null
;
CRITERIA NEW_STR
--------- ------------------------------------
$1 = True Example 1 = True
$2 > $3 Example 2 > Example 3
$1 = $4 Example 1 = Example 2 + Example 3
3 rows selected.
这篇关于用查询中的值替换字段的一部分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!