任何人都可以幫助我調整這種模式，我需要捕獲彼此相鄰的重復字串（而不是其他字串分開）并只保留其中一個。這是我的表達，但我無法得到我想要的輸出：
SELECT
DATA,
REGEXP_REPLACE(DATA, '(.*)(.*)\1','\1\2') TRIMMED_STR
FROM
(
SELECT 'AAA-BBB-FFF-GGG' DATA FROM DUAL
UNION ALL
SELECT 'AAA-BBB-CCC-CCC-AAA-BBB-BBB' DATA FROM DUAL
UNION ALL
SELECT 'AAA-AAA-CCC-DDD-DDD-EEE-AAA' DATA FROM DUAL
UNION ALL
SELECT 'AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB' DATA FROM DUAL
UNION ALL
SELECT 'AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC' DATA FROM DUAL
UNION ALL
SELECT 'AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD' DATA FROM DUAL
) STR
OLD
AAA-BBB-FFF-GGG
AAA-BBB-CCC-CCC-AAA-BBB-BBB
AAA-AAA-CCC-DDD-DDD-EEE-AAA
AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB
AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC
AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD
NEW
AAA-BBB-FFF-GGG
AAA-BBB-CCC-AAA-BBB
AAA-CCC-DDD-EEE-AAA
AAA-BBB-CCC-DDD-EEE-FFF-GGG-BBB
AAA-BBB-CCC-DDD-EEE-FFF-CCC
AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-JJJ-DDD
uj5u.com熱心網友回復：
像這樣的東西？
SQL> with str as
2 (
3 select 'AAA-BBB-FFF-GGG' data from dual
4 union all
5 select 'AAA-BBB-CCC-CCC-AAA-BBB-BBB' data from dual
6 union all
7 select 'AAA-AAA-CCC-DDD-DDD-EEE-AAA' data from dual
8 union all
9 select 'AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB' data from dual
10 union all
11 select 'AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC' data from dual
12 union all
13 select 'AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD' data from dual
14 )
15 select data old,
16 regexp_replace(data, '([^-] )(-[-]*\1) ', '\1') new
17 from str;
OLD NEW
-------------------------------------------------- --------------------------------------------------
AAA-BBB-FFF-GGG AAA-BBB-FFF-GGG
AAA-BBB-CCC-CCC-AAA-BBB-BBB AAA-BBB-CCC-AAA-BBB
AAA-AAA-CCC-DDD-DDD-EEE-AAA AAA-CCC-DDD-EEE-AAA
AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB AAA-BBB-CCC-DDD-EEE-FFF-GGG-BBB
AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC AAA-BBB-CCC-DDD-EEE-FFF-CCC
AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-JJJ-DDD
6 rows selected.
SQL>
uj5u.com熱心網友回復：
您可以使用：
SELECT data,
REGEXP_REPLACE(
data,
'(^|-)([^-] )(-\2) ',
'\1\2'
) AS trimmed_str
FROM table_name
其中，對于樣本資料：
CREATE TABLE table_name (data) AS
SELECT 'AAA-BBB-FFF-GGG' FROM DUAL UNION ALL
SELECT 'AAA-BBB-CCC-CCC-AAA-BBB-BBB' FROM DUAL UNION ALL
SELECT 'AAA-AAA-CCC-DDD-DDD-EEE-AAA' FROM DUAL UNION ALL
SELECT 'AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB' FROM DUAL UNION ALL
SELECT 'AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC' FROM DUAL UNION ALL
SELECT 'AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD' FROM DUAL UNION ALL
SELECT 'AAA-AAA-AAA-AAA' FROM DUAL
輸出：
資料 TRIMMED_STR AAA-BBB-FFF-GGG AAA-BBB-FFF-GGG AAA-BBB-CCC-CCC-AAA-BBB-BBB AAA-BBB-CCC-AAA-BBB AAA-AAA-CCC-DDD-DDD-EEE-AAA AAA-CCC-DDD-EEE-AAA AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB AAA-BBB-CCC-DDD-EEE-FFF-GGG-BBB AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC AAA-BBB-CCC-DDD-EEE-FFF-CCC AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-JJJ-DDD AAA-AAA-AAA-AAA AAA
如果您可以擁有可變長度的術語，那么您可以使用：
WITH bounds (rid, data, prev_pos, start_pos, end_pos) AS (
SELECT ROWID, data, 1, 1, INSTR(data, '-', 1)
FROM table_name
UNION ALL
SELECT rid, data, start_pos, end_pos 1, INSTR(data, '-', end_pos 1)
FROM bounds
WHERE end_pos > 0
),
terms (rid, data, start_pos, prev, term) AS (
SELECT rid,
data,
start_pos,
SUBSTR(data, prev_pos, start_pos - prev_pos - 1),
CASE end_pos
WHEN 0
THEN SUBSTR(data, start_pos)
ELSE SUBSTR(data, start_pos, end_pos - start_pos)
END
FROM bounds
)
SELECT MAX(data) AS data,
LISTAGG(term, '-') WITHIN GROUP (ORDER BY start_pos) AS trimmed_str
FROM terms
WHERE start_pos = 1
OR prev <> term
GROUP BY rid
或者：
SELECT MAX(data) AS data,
LISTAGG(
REGEXP_SUBSTR(data, '[^-] ', 1, LEVEL),
'-'
) WITHIN GROUP (ORDER BY LEVEL) AS trimmed_str
FROM table_name
WHERE LEVEL = 1
OR REGEXP_SUBSTR(data, '[^-] ', 1, LEVEL)
<> REGEXP_SUBSTR(data, '[^-] ', 1, LEVEL - 1)
CONNECT BY
LEVEL <= REGEXP_COUNT(data, '[^-] ')
AND PRIOR ROWID = ROWID
AND PRIOR SYS_GUID() IS NOT NULL
GROUP BY ROWID
或者，從 Oracle 12：
SELECT data,
trimmed_str
FROM table_name t
CROSS JOIN LATERAL (
SELECT LISTAGG(
REGEXP_SUBSTR(data, '[^-] ', 1, LEVEL),
'-'
) WITHIN GROUP (ORDER BY LEVEL) AS trimmed_str
FROM DUAL
WHERE LEVEL = 1
OR REGEXP_SUBSTR(data, '[^-] ', 1, LEVEL)
<> REGEXP_SUBSTR(data, '[^-] ', 1, LEVEL - 1)
CONNECT BY
LEVEL <= REGEXP_COUNT(data, '[^-] ')
)
對于上述資料和附加行，兩者都輸出AAA-AAA-AAA-AAABBB-BBB：
資料 TRIMMED_STR AAA-BBB-FFF-GGG AAA-BBB-FFF-GGG AAA-BBB-CCC-CCC-AAA-BBB-BBB AAA-BBB-CCC-AAA-BBB AAA-AAA-CCC-DDD-DDD-EEE-AAA AAA-CCC-DDD-EEE-AAA AAA-BBB-BBB-CCC-DDD-EEE-EEE-FFF-GGG-BBB AAA-BBB-CCC-DDD-EEE-FFF-GGG-BBB AAA-BBB-CCC-DDD-EEE-EEE-FFF-FFF-CCC AAA-BBB-CCC-DDD-EEE-FFF-CCC AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-III-JJJ-DDD AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH-III-JJJ-DDD AAA-AAA-AAA-AAA AAA AAA-AAA-AAA-AAABBB-BBB AAA-AAABBB-BBB
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/417700.html
標籤：