This CASE statement extracts the actual tracker link out of the weird markdown formatting that some of them are coming across in, hopefully to prevent the search bar from freaking out and taking down our whole build.
For the record, I do know that a regular expression would be better here and I had a wonderful clean solution that worked great in dbeaver, but due to escape characters being needed and then the regex no longer matching correctly once the escape characters were added, we went with this instead. Here's the nicer CASE statement that works in dbeaver in case @ItsMeBrianD has some magical way to make it work in Evidence:
CASE
WHEN pa.tracker ~ '\[([^\]]+)\]' THEN regexp_replace(pa.tracker, '.*\[(.*)\].*', '\1')
ELSE pa.tracker
END AS cleaned_tracker
This CASE statement extracts the actual tracker link out of the weird markdown formatting that some of them are coming across in, hopefully to prevent the search bar from freaking out and taking down our whole build.
For the record, I do know that a regular expression would be better here and I had a wonderful clean solution that worked great in dbeaver, but due to escape characters being needed and then the regex no longer matching correctly once the escape characters were added, we went with this instead. Here's the nicer CASE statement that works in dbeaver in case @ItsMeBrianD has some magical way to make it work in Evidence: