配置单元中的拆分函数

p1iqtdky 于 2021-06-26 发布在 Hive

关注(0)|答案(1)|浏览(344)

我想把地址分成两列，分别是streetno和streetname，比如说对于ex select address1 from customer
前男友的地址看起来像

2719 STONE CREEK DR

把它们放在街上 2719 街道名称为 STONE CREEK DR .
使用 split(address1 ,'[\ ]')[0] 只是分开街道不只是。
我们只是使用select语句来查看数据。
样本数据：

address1
100 HORACE GREELEY RD  
55 School Street
2893 MASHIE CIR
1200 JEWEL DR
201 W RIVER RD

预期产量

+--------------+---------------------+
| streetnumber |     streetname      |
+--------------+---------------------+
|          100 | HORACE GREELEY RD   |
|           55 | School Street       |
|         2893 | MASHIE CIR          |
|         1200 | JEWEL DR            |
|          201 | W RIVER RD          |
+--------------+---------------------+

Hive hiveql

来源：https://stackoverflow.com/questions/46222440/split-function-in-hive

1条答案

按热度按时间

4uqofj5v1#

select  regexp_extract(address1,'(.*?)\\s',1)   as streetnumber
       ,regexp_extract(address1,'\\s(.*)' ,1)   as streetname 

from    mytable
;

+----------------+--------------------+
| streetnumber   |     streetname     |
+----------------+--------------------+
| 100            | HORACE GREELEY RD  |
| 55             | School Street      |
| 2893           | MASHIE CIR         |
| 1200           | JEWEL DR           |
| 201            | W RIVER RD         |
+----------------+--------------------+

或者

select  regexp_extract(address1,'.*?(?=\\s)',0) as streetnumber
       ,regexp_extract(address1,'(?<=\\s).*',0) as streetname 

from    mytable
;

+----------------+--------------------+
| streetnumber   |     streetname     |
+----------------+--------------------+
| 100            | HORACE GREELEY RD  |
| 55             | School Street      |
| 2893           | MASHIE CIR         |
| 1200           | JEWEL DR           |
| 201            | W RIVER RD         |
+----------------+--------------------+

或者

select  split(address1,'\\s')[0]      as streetnumber
       ,split(address1,'^.*?\\s')[1]  as streetname 

from    mytable
;

+----------------+--------------------+
| streetnumber   |     streetname     |
+----------------+--------------------+
| 100            | HORACE GREELEY RD  |
| 55             | School Street      |
| 2893           | MASHIE CIR         |
| 1200           | JEWEL DR           |
| 201            | W RIVER RD         |
+----------------+--------------------+

赞(0）回复(0）举报 2021-06-26

我来回答

配置单元中的拆分函数

1条答案

或者

或者

相关问题

热门标签

最新问答