oracle查询某个字符之前的全部字符串,在包含它自己的oracle的字符串之前获取所有内容(get everything before a string including itself oracl...-CSDN博客

The solution will have to work when the "token" ( the '81' in your example ) appears between two slashes, or right at the beginning of the string and before a slash, or right after the last slash at the end of the string. It should not match if '81' appears as part of a "token" (between slashes or before the first or after the last slash). Also, if the "token" appears more than once, it should be replaced (with everything before it) only once, and if it doesn't appear at all, then the original string should be unchanged.

If these are the rules, then you can do something like I show below. If any of the rules are different, the solution can be modified to accommodate.

I created a few input strings to test all these cases in a WHERE clause. I also created the "search token" and the "replacement text" in a second subquery in the WITH clause. The entire WITH clause should be replaced - it is not part of the solution, it is only for my own testing. In the main query you should use your actual table and column names (and/or hardcoded text).

I use REGEXP_REPLACE to find the token and replace it and everything that comes before it (but not the slash after it, if there is one) with the replacement text. I must be careful with that slash after the search token; I use a backreference in the replacement string in REGEXP_REPLACE for that purpose.

with

event_dynamic_attribute ( attribute_value ) as (

select '28/29/81/732536/1496071' from dual union all

select '29/33/530813/340042/88' from dual union all

select '81/6883/3902/81/993' from dual union all

select '123/45/6789/81' from dual

substitution ( token, replacement ) as (

select '81', 'mathguy is great' from dual

)

select attribute_value,

regexp_replace (attribute_value, '(^|.*?/)' || token || '(/|$)',

replacement || '\2', 1, 1) new_attrib_value