extract substring after certain word(s)
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have a long string where I am looking to extract two values:
1: The numeric value that appears after "fullAcctNumber"
2. The string (can be multiple words) that appears after "Instrument, paramVal=".
As in the attached example, there can be multiple instances within the same string. I need to extract all instances within the string
ideal output (can be in multiple columns or rows):
"123456789:Individual"
"987654321:IRA"
string example:
acctType=INV, extAcctType=INV, acctNumber=123456789), AcctName(paramName=NickName, paramVal=Bobs Brokerage)], acctOwnerName=null, fullAcctNumber=123456789), acctDateTime=[AcctDateTime(dateTimeType=LastUpdateAttempt, dateTimeVal=2023-09-09), property=[Property(paramName=RetirementStatus, paramVal=N R), Property(paramName=Instrument, paramVal=Individual), Property(paramName=AccountOwnership, paramVal=Individual)]), acctType=INV, extAcctType=INV, acctNumber=987654321), AcctName(paramName=NickName, paramVal=Bobs IRA), acctOwnerName=null, fullAcctNumber=987654321), acctDateTime=[AcctDateTime(dateTimeType=LastUpdateAttempt, dateTimeVal=2023-09-09), property=[Property(paramName=RetirementStatus, paramVal=R), Property(paramName=Instrument, paramVal=IRA), Property(paramName=AccountOwnership, paramVal=Individual)])])]))
Solved! Go to Solution.
- Labels:
- Regex
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@FinnCharlton This is great!! Huge help!! Thank you.
For the below section of the expression, how could we modify it to look for multiple words? It looks like it is stopping when there is a space. The values commonly have spaces in them (ex: Rollover IRA, Roth IRA, Brokerage, Joint, Trust, etc). If it helps, there will always be a closed parenthesis at the end of the string.
Instrument, paramVal=\w+
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Drew502 very good point, you can tell it to take everything it finds before the bracket, like this:
Instrument, paramVal=[^\)]+
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@FinnCharlton Thanks again for your help on this! Unfortunately my data has changed and the reg ex broke. I've tried playing around with it but I have a feeling the quotes are what is throwing me off.
As a reminder, I have a giant string that I am trying to return 2 values
1: The numeric value that appears after "FullAcctNumber\" : \" (note: the number of astrix can vary)
2. The string (can be multiple words) that appears after Instrument\",\n \"ParamVal\" : \"
There can be multiple instances of both values
Example:
"FullAcctNumber\" : \"*****1234" ................................. Instrument\",\n \"ParamVal\" : \"Roth IRA\"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @Drew502, try this Regex with the Tokenize option:
"FullAcctNumber\\" : \\"\d+?"|Instrument\\",\\n \\"ParamVal\\" : \\".+?\\"
![](/skins/images/12A9B4B958288E867BE947DD48612FB8/responsive_peak/images/icon_anonymous_message.png)