Hive query to replace only the first occurrence of a substring

Question

I need to replace the first occurrence of a substring from the given string.
Eg If the string is "My name is Adam" and I want to replace the first "a" with "@".
So my desired output is "My n@me is Adam".
In MySQL, there is a function regexp_replace which has an optional parameter occurrence to specify how many occurrences to replace. But unfortunately, that optional parameter is not present in the hive function. Any suggestions?

Answer 1

hive> select regexp_replace('My name is Adam','^(.*?)a','$1@');
OK
My n@me is Adam
Time taken: 0.061 seconds, Fetched: 1 row(s)

Pattern '^(.*?)a' means:

  ^ - the beginning of the string
.*? - any character (.) zero or more times (*) not greedy (?)
 () - remember group, we will refer it in the replacement string as $1
  a - 'a' character literally

Replacement string '$1@' means:

$1 - group number one in the pattern (everything before 'a')
 @ - '@' character literally

You can debug regexp here: regex101.com