How to select all text in string outside of two specific phrases?

I have a column full of strings. I want to select all text in each string outside of the following phrases: /*** ***/

Input:

my_strings
A /*** random words here ***/ B C D E H
P /*** ewofneowi ewoifn::123 ewofin wowefn ***/ G H D K

Desired Output:

A B C D E H
P G H D K

I feel like the easiest way to do this would be through RegEx. I'm trying to do something like regexp_replace(), and replacing what's between the two delimiters with an empty string (''), but can't get this to work. Would appreciate any help!

CodePudding user response：

Since the author of this question already successfully tested this from my comment, I will add it here to make it easier to find if someone else has the same question. Main idea:

SELECT CONCAT(LEFT(yourcolumn, charindex('/***', yourcolumn) - 1), 
SUBSTRING(yourcolumn, charindex('***/', yourcolumn)  4 , 
LEN(yourcolumn)))
FROM yourtable;

Example usage: db<>fiddle