我有一个Hive表如下:
+----+---------------+-------------+
| id | name | partnership |
+----+---------------+-------------+
| 1 | sachin sourav | first |
| 2 | sachin sehwag | first |
| 3 | sourav sehwag | first |
| 4 | sachin_sourav | first |
+----+---------------+-------------+
在这个表中,我需要用“st”替换“sachin”,用“sg”替换“sourav”。我使用下面的查询,但它不能解决问题。
查询:
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;
结果:
+----+---------------+-------------+---------------+
| 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 |
+----+---------------+-------------+---------------+
问题:我的意图是,当id=1时,newname列的值应该是“stsg”。我的意思是它应该替换两个字符串。
1条答案
按热度按时间ngynwnxp1#
可以嵌套替换:
你不需要正则表达式,所以只要使用
replace()
.