Question: need to B fields in A table contains the following content value all deleted

Need to delete the content: the object of the contract sent a day of May 1, 2018,/
The securities mash for variable 11 characters

CodePudding user response:

To the test data, and your want as a result, a representative data,

CodePudding user response:

The replace (b, 'object of the contract sent a day of May 1, 2018,/a ^ z', ')

CodePudding user response:

Give out the test data well

CodePudding user response:

With tab1 as (
Select 'aabbccddeeff SRC from dual union all
Select 'aabbccddeeffgggggggggggggg from dual
),
Tab2 as (
Select the 'bb' STR from dual union all
Select the 'ee' from dual union all
Select 'ff' from dual
)
, tab3 as (
Select the SRC, result_ from tab1 t1, tab2 t2
Where 1=1
And instr (t1) SRC, t2. STR) & gt; 0
The model
Partition by (SRC)
Dimension by (row_number () over (partition by t1. The SRC order by t1. The SRC) rn)
Measures (STR, SRC result_)
Rules (

Result_ (rn)=regexp_replace (NVL (result_] [CV () - 1, result_ [CV ()]), STR [CV ()])
)
)
, tab4, as (
Select the SRC, row_number () over (partition by t1. The SRC order by length (result_) rn, result_ from tab3 t1
)
Select * from tab4, t1
Where a t1. Rn=1
;