一个字符串的：
SELECT LISTAGG(NVL(NULLIF(SUBSTR(str1,LEVEL,1),'8'),SUBSTR(str2,LEVEL,1))) WITHIN GROUP (ORDER BY LEVEL)
FROM (SELECT '1G8M8WQ' str1,'12345UY' str2 FROM DUAL)
CONNECT BY LEVEL<=LENGTH(str1)
1G3M5WQ
多个字符串的：
WITH t AS (
SELECT 1 id,'1G8M8WQ' str1,'12345UY' str2 FROM DUAL
UNION ALL
SELECT 2 id,'TEST18ASDF8123' str1,'98723472390470' str2 FROM DUAL
UNION ALL
SELECT 3 id,'673#$#$8-+ER^%$838KLJRT' str1,'PJ;LQWREKJRFDSLKJEROIWE' str2 FROM DUAL
)
SELECT id,LISTAGG(NVL(NULLIF(SUBSTR(str1,LEVEL,1),'8'),SUBSTR(str2,LEVEL,1))) WITHIN GROUP (ORDER BY LEVEL)
FROM t
CONNECT BY id = PRIOR id AND LEVEL<=LENGTH(str1) AND PRIOR SYS_GUID() IS NOT NULL
GROUP BY id;
11G3M5WQ
2TEST14ASDF0123
3673#$#$E-+ER^%$K3EKLJRT
如果没有LISTAGG:
WITH t AS (
SELECT 1 id,'1G8M8WQ' str1,'12345UY' str2 FROM DUAL
UNION ALL
SELECT 2 id,'TEST18ASDF8123' str1,'98723472390470' str2 FROM DUAL
UNION ALL
SELECT 3 id,'673#$#$8-+ER^%$838KLJRT' str1,'PJ;LQWREKJRFDSLKJEROIWE' str2 FROM DUAL
)
SELECT id,REPLACE(MAX(SYS_CONNECT_BY_PATH(NVL(NULLIF(SUBSTR(str1,LEVEL,1),'8'),SUBSTR(str2,LEVEL,1)),'\')),'\')
FROM t
CONNECT BY id = PRIOR id AND LEVEL<=LENGTH(str1) AND PRIOR SYS_GUID() IS NOT NULL
GROUP BY id;
|
