HIVE的一点高级用法

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

1、只获取文本中的字母，汉字和数字，剔除标点和特殊符号

1. select regexp_replace('assseewfew@@@###$%%.....~~~京津冀123','[[^0-9a-zA-Z\\u4e00-\\u9fa5]]','')

复制代码

1. assseewfew京津冀123

复制代码

2、获取字符串a首次在字符串b中出现的位置

1. select locate('123','124123')

复制代码

1. 4

复制代码

1. select sort_array(collect_set(concat_ws(':',cast(sn as string),sp))) from
   
2. (select locate(sp,'妈妈做的土豆炖排骨') as sn, sp from item_ci
   
3. where locate(sp,'妈妈做的土豆炖排骨')>0)a

复制代码

1. ["5:土豆","8:排骨"]

复制代码

3、比较两个字符串相同的字符数量

1. select levenshtein('秘制红烧肉盖饭十炒时蔬+赠饮','红烧肉盖饭时蔬')

复制代码

1. 7

复制代码

4、解析json组

1. #需要解析的json

复制代码

1. select
   
2.   get_json_object(ss.list, '$.sid') as sid,
   
3.   get_json_object(ss.list, '$.price') as price,
   
4.   get_json_object(ss.list, '$.sn') as sn,
   
5.   get_json_object(ss.list, '$.number') as number,
   
6.   get_json_object(ss.list, '$.outr') as outr,
   
7.   get_json_object(ss.list, '$.deli') as deli,
   
8.   get_json_object(ss.list, '$.outs') as outs,
   
9.   get_json_object(ss.list, '$.act') as act
   
10. from
    
11.   (
    
12.     select
    
13.       split(
    
14.         regexp_replace(
    
15.           regexp_extract(
    
16.             '[{"sid":"3458764527033385083","sn":1,"number":200,"price":33,"outr":0,"outs":0,"deli":0,"act":["3458764719724101804"]},{"sid":"3458764702871191593","sn":1,"number":2000,"price":39,"outr":0,"outs":0,"deli":0,"act":["3458764730620903593"]},{"sid":"3458764712379678782","sn":1,"number":900,"price":42,"outr":0,"outs":0,"deli":0,"act":["3458764738128707753"]}]',
    
17.             '^\\[(.+)\\][table=98%]
    
18.   [tr]  [td=87]sid[/td]  [td=87]price[/td]  [td=87]sn[/td]  [td=87]number[/td]  [td=87]outr[/td]  [td=87]deli[/td]  [td=87]outs[/td]  [td=87]act[/td] [/tr]
    
19. [tr]  [td]3458764527033385083[/td]  [td]33[/td]  [td]1[/td]  [td]200[/td]  [td]0[/td]  [td]0[/td]  [td]0[/td]  [td]["3458764719724101804"][/td] [/tr]
    
20. [tr]  [td]3458764702871191593[/td]  [td]39[/td]  [td]1[/td]  [td]2000[/td]  [td]0[/td]  [td]0[/td]  [td]0[/td]  [td]["3458764730620903593"][/td] [/tr]
    
21. [tr]  [td]3458764712379678782[/td]  [td]42[/td]  [td]1[/td]  [td]900[/td]  [td]0[/td]  [td]0[/td]  [td]0[/td]  [td]["3458764738128707753"][/td] [/tr]
    
22. [/table]
    
23. 
    
24. 
    
25. 
    
26. 
    
27. 
    
28. 
    
29. 
    
30. 
    
31. ,
    
32.             1
    
33.           ),
    
34.           '\\}\\,\\{',
    
35.           '\\}\\|\\|\\{'
    
36.         ),
    
37.         '\\|\\|'
    
38.       ) as str
    
39. ) pp lateral view explode(pp.str) ss as list

复制代码

sid	price	sn	number	outr	deli	outs	act
3458764527033385083	33	1	200	0	0	0	["3458764719724101804"]
3458764702871191593	39	1	2000	0	0	0	["3458764730620903593"]
3458764712379678782	42	1	900	0	0	0	["3458764738128707753"]

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：特殊符号 字符串 京津冀 炖排骨 红烧肉

select
  get_json_object(ss.list, '$.sid') as sid,
  get_json_object(ss.list, '$.price') as price,
  get_json_object(ss.list, '$.sn') as sn,
  get_json_object(ss.list, '$.number') as number,
  get_json_object(ss.list, '$.outr') as outr,
  get_json_object(ss.list, '$.deli') as deli,
  get_json_object(ss.list, '$.outs') as outs,
  get_json_object(ss.list, '$.act') as act
from
  (
    select
      split(
        regexp_replace(
          regexp_extract(
            '[{"sid":"3458764527033385083","sn":1,"number":200,"price":33,"outr":0,"outs":0,"deli":0,"act":["3458764719724101804"]},{"sid":"3458764702871191593","sn":1,"number":2000,"price":39,"outr":0,"outs":0,"deli":0,"act":["3458764730620903593"]},{"sid":"3458764712379678782","sn":1,"number":900,"price":42,"outr":0,"outs":0,"deli":0,"act":["3458764738128707753"]}]',
            '^\\[(.+)\\]$',
            1
          ),
          '\\}\\,\\{',
          '\\}\\|\\|\\{'
        ),
        '\\|\\|'
      ) as str
) pp lateral view explode(pp.str) ss as list

select
  get_json_object(ss.list, '$.sid') as sid,
  get_json_object(ss.list, '$.price') as price,
  get_json_object(ss.list, '$.sn') as sn,
  get_json_object(ss.list, '$.number') as number,
  get_json_object(ss.list, '$.outr') as outr,
  get_json_object(ss.list, '$.deli') as deli,
  get_json_object(ss.list, '$.outs') as outs,
  get_json_object(ss.list, '$.act') as act
from (select split(regexp_replace(regexp_extract(
'[{"sid":"3458764527033385083","sn":1,"number":200,"price":33,"outr":0,"outs":0,"deli":0,"act":["3458764719724101804"]},{"sid":"3458764702871191593","sn":1,"number":2000,"price":39,"outr":0,"outs":0,"deli":0,"act":["3458764730620903593"]},{"sid":"3458764712379678782","sn":1,"number":900,"price":42,"outr":0,"outs":0,"deli":0,"act":["3458764738128707753"]}]',
'^\\[(.+)\\]$',1),'\\}\\,\\{','\\}\\|\\|\\{'),'\\|\\|') as str
) pp lateral view explode(pp.str) ss as list