最后一次出现分隔符后的子字符串 [英] substring after the last occurrence of a delimiter

问题描述

大家好，

我有一个字符串，如所有〜消费者和社区银行（S577006）〜按揭贷款（S531306）〜抵押生产和服务（S570307）~MBT ACCESS PLUS（54966）



通过这个我必须提取最后一个节点MBT ACCESS PLUS（54966）



你能建议吗？没有硬编码..而且我只能使用内置函数和正则表达式...请不要建议拆分和循环...我不能写多个子查询。



问候，

Sandeep

Hi All,
I have a string like "ALL~CONSUMER & COMMUNITY BANKING (S577006)~MORTGAGE BANKING (S531306)~MORTGAGE PRODUCTION AND SERVICING (S570307)~MBT ACCESS PLUS (54966)"

through this I have to extract the last node that is "MBT ACCESS PLUS (54966)"

Can you please suggest. No hardcoding .. and I have to use only in-built function and Regex... Please dont suggest splitting and doing a loop... I cant write multiple subquery.

Regards,
Sandeep

推荐答案

试试这个..

Try this..

SELECT COLUMN_1 FROM(
SELECT REGEXP_SUBSTR(
'ALL~CONSUMER & COMMUNITY BANKING (S577006)~MORTGAGE BANKING (S531306)~MORTGAGE PRODUCTION AND SERVICING (S570307)~MBT ACCESS PLUS (54966)','[^~]+', 1, level)
AS COLUMN_1
FROM DUAL
CONNECT BY REGEXP_SUBSTR('ALL~CONSUMER & COMMUNITY BANKING (S577006)~MORTGAGE BANKING (S531306)~MORTGAGE PRODUCTION AND SERVICING (S570307)~MBT ACCESS PLUS (54966)',
'[^~]+', 1, LEVEL) IS NOT NULL
ORDER BY ROWNUM DESC)  WHERE ROWNUM = 1 ;

或

OR

SELECT substr('ALL~CONSUMER & COMMUNITY BANKING (S577006)~MORTGAGE BANKING (S531306)~MORTGAGE PRODUCTION AND SERVICING (S570307)~MBT ACCESS PLUS (54966)',
        instr('ALL~CONSUMER & COMMUNITY BANKING (S577006)~MORTGAGE BANKING (S531306)~MORTGAGE PRODUCTION AND SERVICING (S570307)~MBT ACCESS PLUS (54966)','~',1,
 regexp_count('ALL~CONSUMER & COMMUNITY BANKING (S577006)~MORTGAGE BANKING (S531306)~MORTGAGE PRODUCTION AND SERVICING (S570307)~MBT ACCESS PLUS (54966)','~'))+1)
FROM dual;

这篇关于最后一次出现分隔符后的子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！