REGEXP_REPLACE("My dog is funny and happy", r"(\S \S \S )", r"*") This is my SQL for achieving this. My output should look something like this = My dog is funny *and happy

When I try the above query it removes the first few words. How do I work this out?

CodePudding user response：

You should use a backreference:

REGEXP_REPLACE("My dog is funny and happy", r"^((?:\S \s ){4})", r"\1*")
REGEXP_REPLACE("My dog is funny and happy", r"^(?:\S \s ){4}", r"\0*")

See the regex demo. Details:

• ^ - start of string
• ((?:\S \s ){4}) - Group 1 (\1 in the replacement will refer to this group value): four occurrences of one or more non-whitespaces followed with one or more whitespaces.

\0 refers to the whole match value.

See the regexp_replace reference:

REGEXP_REPLACE(value, regexp, replacement)

Returns a STRING where all substrings of value that match regular expression regexp are replaced with replacement.

You can use backslashed-escaped digits (\1 to \9) within the replacement argument to insert text matching the corresponding parenthesized group in the regexp pattern. Use \0 to refer to the entire matching text.