sql—用不同的替换替换来替换databasea列中的多个字符串

laik7k3q  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(342)

我有一个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”。我的意思是它应该替换两个字符串。

ngynwnxp

ngynwnxp1#

可以嵌套替换:

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

你不需要正则表达式,所以只要使用 replace() .

相关问题