[SOLVED] regexp_replace to replace the first match from the back of a string, and return the full string

regexp_replace to replace the first match from the back of a string, and return the full string

I have a string that reads "dont replace this text, but instead replace the last text <-- this one".

I want to replace the first match from the back of the string, and return the full string, as such: "dont replace this text, but instead replace the last ONE <-- this one"

select regexp_replace("dont replace this text, but instead replace the last text <-- this one", "\\b(text)\\b", "ONE")

Doing the above will replace both instances of "text" with "ONE", which isn't what I want.

I tried :

select regexp_replace("dont replace this text, but instead replace the last text <-- this one", "\\b.*(text)\\b", "ONE")

but this one cuts off everything before the match and I'm left with "ONE <-- this one"

Solution

If Hive's regex support negative lookaheads, you could use this version:

SELECT REGEXP_REPLACE(
    'dont replace this text, but instead replace the last text',
    '\\btext\\b(?!.*\\btext\\b)',
    'ONE'
)