拆分字符串，爆炸，检查每个部分是否与regexp匹配，收集匹配部分的数组，如果需要从数组中获取字符串，使用concat_ws()来连接数组。

with your_data as(
select stack(1, 'hosts: 192.168.1.1 192.168.1.2 host' ) as hosts
)

select collect_set(case when part rlike '((25[0-5]|2[0-4]\\d|[01]?\\d\\d?)\\.){3}(25[0-5]|2[0-4]\\d|[01]?\\d\\d?)' 
                        then part 
                  else null end )
from your_data d
     lateral view explode(split(hosts, ' +')) s as part;

result:

["192.168.1.1","192.168.1.2"]