How I can get positive numbers from a String and separates with commas-CodePudding

I have the following problem in Oracle, I cannot get the following through regex:

For example, I have this String:

C_1   123 * C_2 * 4332

How can I get the following result?

123,4332

Right now what I have done is:

SELECT regexp_replace('C_1   123 * C_2 * 4332','C_[0-9.] |[[:punct:]]|[[:space:]]', '') FROM DUAL;

But in this case, I get 1234332.

CodePudding user response：

You can use

SELECT trim(BOTH ',' FROM regexp_replace('C_1   123 * C_2 * 4332', '(C_\S |\D) ', ',')) FROM DUAL;

See the online demo.

The (C_\S |\D) regex matches one or more repetitions of C_ followed with one or more non-whitespace chars or any non-digit char, and the matches are replaced with a single ,.

As the match can occur at the start or end of string, the trim function is used to post-process the regexp_replace result.