I'm struggling with splitting string in two parts by delimiter, which is a dash ("-"): "sample part 1-sample part 2"
The problem is that sometimes the text contains a dash (and should be split into two new fields, Say "Output 1" and "Output 2"), sometimes not which means that there is no Output 2 and that field should be left blank. Also, sometimes the text contains dashes which shouldn't be split (in all cases such text is in brackets, like "sample part 1(non-split)-sample part 2")
So the desired output is like follow:
| Source | Output 1 | Output 2 |
| sample part 1-sample part 2 | sample part 1 | sample part 2 |
| sample part 1 | sample part 1 | |
| sample part 1(non-split)-sample part 2 | sample part 1(non-split) | sample part 2 |
I managed to properly capture the first part by using Regex_Replace([Source], "(.*)-(?!.*\)).*", "$1") , but can't reliably handle the second part.
I was able to properly capture it when it exists, but if it is not, then I have the same values in Otuput 1 and Output 2 instead of having Output 2 just blank/empty
