我有一個表格，其中有一個列反饋，這是來自前端的免費文本。此列具有以下值 -
FEEDBACK
-Agent was listening and very attentive.
Agent showed all the houses and gave the right description
Agent was well versed & knew how to speak multiple
languages
-<p>Agent was well dressed for the event</p>
由于這是復制粘貼的，因此后端有時會在兩行之間有許多空格或空行。
我想洗掉所有這些并顯示輸出，如 -
FEEDBACK
-Agent was listening and very attentive.
Agent showed all the houses and gave the right description
Agent was well versed & knew how to speak multiple
languages
-Agent was well dressed for the event
為此，我使用以下查詢 -
select REGEXP_REPLACE(regexp_replace( regexp_replace(
regexp_replace(
DBMS_LOB.SUBSTR(max(feedback),4000),
/*
Replace LF followed by any non-printable sequence that ends with newline
with single newline
*/
chr(10) || '[^[:graph:]]*(' || chr(13) || '?' || chr(10) || ')',
chr(10) || '\1'
),
/*Then replace newline repetitions*/
'(' || chr(13) || '?' || chr(10) || ') ',
'\1'
),'<.*?>'),' ') as feedback
from dual;
有什么辦法可以合并這些 regex_replace 而不是使用多個 regex_replace 來滿足我的要求？
uj5u.com熱心網友回復：
不是所有的都可以組合。
但有些可以，通過正則運算式 OR|
然后最好先更換那些。
因為洗掉它們可能會導致額外的空行。
SELECT
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(DBMS_LOB.SUBSTR(feedback, 4000)
, '( )|(<[/[:alpha:]] >)')
, '[[:space:]] $','',1,0,'m')
, '(['||chr(13)||']?['||chr(10)||']){2,}','\1') AS feedback
FROM your_table
ORDER BY feedback DESC
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/387424.html
上一篇：如何計算隨時間移動的平均值？
下一篇：delete陳述句之間的區別