我有一列回傳以下輸出：
PERSON_NUMBER FEEDBACK
13636 -Very attentive during our sessions
-Very interative session, questions
were asked and answered well.
Nice turn over
Debates were good
我希望 FEEDBACK Column 的輸出看起來像：
PERSON_NUMBER FEEDBACK
13636 -Very attentive during our sessions
-Very interative session, questions
were asked and answered well.
Nice turn over
Debates were good
即應洗掉行之間的額外空格。
uj5u.com熱心網友回復：
你可以做一個正則運算式替換(?:\r?\n){2,}，只用一個 CR?LF 替換：
SELECT PERSON_NUMBER,
REGEXP_REPLACE(FEEDBACK,
'(' || chr(13) || '?' || chr(10) || '){2,}',
chr(13) || chr(10)) AS FEEDBACK
FROM yourTable;
uj5u.com熱心網友回復：
如果您需要洗掉所有看起來為空的行（例如，還可能包含空格、制表符或其他不可列印的字符），那么您可以[:graph:]在正則運算式中使用類的否定來查找此類行并將它們替換為空行和然后用一個替換所有換行重復。
下面是代碼：
with a as ( select q'[-Very attentive during our sessions -Very interative session, questions were asked and answered well.]' || chr(13) || chr(10) || q'[ ]' || chr(10) || q'[ ]' || chr(10) || chr(10) || q'[Nice turn over]' as q from dual ) select regexp_replace( regexp_replace( q, /* 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 q from a
| q |
|---|
| - 在我們的會議期間非常專心 - 非常互動的會議，問題 被提出并得到很好的回答。 不錯的翻身 |
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/389871.html