Replacing multiple strings from a databsae column with distinct replacements

Question

I have a hive table as below:

+----+---------------+-------------+
| id | name          | partnership |
+----+---------------+-------------+
| 1  | sachin sourav | first       |
| 2  | sachin sehwag | first       |
| 3  | sourav sehwag | first       |
| 4  | sachin_sourav | first       |
+----+---------------+-------------+

In this table I need to replace strings such as "sachin" with "ST" and "Sourav" with "SG". I am using following query, but it is not solving the purpose.

Query:

select 
    *,
    case
       when name regexp('\\bsachin\\b') 
          then regexp_replace(name,'sachin','ST')
       when name regexp('\\bsourav\\b') 
          then regexp_replace(name,'sourav','SG')
       else name
    end as newName
from sample1;

Result:

+----+---------------+-------------+---------------+
| id | name          | partnership | newname       |
+----+---------------+-------------+---------------+
| 4  | sachin_sourav | first       | sachin_sourav |
| 3  | sourav sehwag | first       | SG sehwag     |
| 2  | sachin sehwag | first       | ST sehwag     |
| 1  | sachin sourav | first       | ST sourav     |
+----+---------------+-------------+---------------+

Problem: My intention is, when id = 1, the newName column should bring value as "ST SG". I mean it should replace both strings.

Answer 1

You can nest the replaces:

select s.*,                                             
       replace(replace(s.name, 'sachin', 'ST'), 'sourav', 'SG') as newName
from sample1 s;

You don't need regular expressions, so just use replace().