I have two fields:
Field 1 contain a single value (like COMPANY NAME 1),
Field 2 contain list of values, delimited with "|", among which there may be (or not) the value matching Field 1 (so it might be i.e. Company Name 1|Company Name 2|... or Company Name 2, or Company Name 3|Company Name 4, etc.)
Values in F1 are all uppercase, values in F2 are not.
I want to remove all values matching with ones listed in Field 1 (So if Field 2 is Company Name 1|Company Name 2, and Field 1 is COMPANY NAME 2, then I want to have only Company Name 1 in F2
Since Replace Function is case sensitive, I wanted to use Regex_Replace function for that, written in a way: Regex_Replace([F2], [F1], "") so it will remove values (and I handle potentially doubled separators in the next step)
This works only on part of the data - there area values in F1 which are exactly matching (except the case) with F2, but are not removed.
I don't know what is the reason, I looked for any values which are same visually but are considered as different (like space and non-breaking space), any spaces on the end or beginning of values stored in F1, but couldn't find anything.
Surprisingly, if I use Uppercase function on Field 2 and then use standard Replace function - Replace([F1], [F2], "") everything works as expected.
Regex_Replace still doesn't work as expected,
Any idea why standard Replace works and Regex_Replace doesn't? There isn't any multiplication of values stored in F2, every value is appearing exactly once (sometimes F2 contains only the value matching F1 and still it isn't removed by Regex_Replace, while standard Replace works).
I want to keep original formating in F2, so using UPPERCASE to handle that with the standard REPLACE and then reverting remaining values to their original formating is possible, but requires additional workarounds.
And except that I would like to understand why Regex_Replace doesn't work?
