Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted

1 solution

Try this..

SQL
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

SQL
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;
 
Share this answer
 
v3
Comments
Sandeep Kumar Sinha 17-Feb-14 6:55am    
hi ,

I cant write multiple subquery there. Itried to use regular expression

regexp_substr(column_name, ''''[^~]+'''', 1, 8) as lastnode

it is working fine, but the concern is it is a variabel string , as in upper query you can see I have hardcoded 8th index of the query.

Can you please suggest me how to modify and putting the last string index after the delimiter.

Thanks,
Sandeep
s#@!k 18-Feb-14 1:47am    
hi, check the updated solution
I think there is no subquery, you can replace your variable in the hard coded string.
or you will get some idea for your solution.
Sandeep Kumar Sinha 19-Feb-14 8:54am    
Yes it worked .. thanks a lot :) I actually need REGEXP_COUNT .. thank you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900