My suggestion for the regular expression in reg_extract is
Second capture can occur 0 or 1 time. String must end with the third capture, this is a right parenthesis.
You can use for both fields reg_extract or you extract the first word (location) with substr function like this:
which is faster.
Hope this helps and i did not put any errors in the expressions.
Thank you for your suggestion. But when I use the regex expression suggested for location code it is returning the location.
I would follow a completely different approach.
Instead of using regular expressions, I would search for the positions of the first and second opening and closing parenthese in the input string (using INSTR() function).
If the positions of the second opening and closing parenthese are > 0, then these contain the location code while the first pair of parentheses belongs to the location itself.
If the positions of the second opening and closing parenthese are = 0, then there is only one pair of parentheses, meaning that these enclose the location code and everything before them is the location.
Cutting out the respective parts using SUBSTR() is easy once you have identified whether there are one or two pairs of parentheses.
Not to forget that this approach is far faster; regular expressions are pretty expensive in terms of CPU time.
For the location code you can use the REVERSE() function then last set of parenthesis will always be the first set no matter what.
The REVERSE() function must be applied on the result as well of course.
Hope this helps,
This sounds like a good approach. How do I search for both opening and closing parentheses occurences? It appears you can only search for one string? Could you provide examples?
If you want to go for Nico's solution you can use 2 variables which you can use in the SUBSTR() function.
VAR_1 IIF(INSTR(location_field,'(',1,2) > 0,INSTR(location_field,'(',1,2),INSTR(location_field,'(',1,1))
VAR_2 IIF(INSTR(location_field,')',1,2) > 0,INSTR(location_field,')',1,2),INSTR(location_field,')',1,1))
For the output port with location_code you can use the below logic.
SUBSTR(location_field, (VAR_1 + 1), (VAR_2 - (VAR_1 + 1)))
Why VAR_1 + 1, well you don't want to incorporate the parenthesis in your location code.
This worked like a charm. Thank you for your input.
Thanks for providing more details to my idea, JanLeendert!
Bobby, I'm glad that it works fine for you.