Impala中的REGEXP

admin

Impala中的REGEXP_EXTRACT

sql

我试图弄清楚如何从看起来像这样的字符串中提取客户ID：

{"param":"success","value":"10","level":"0","error_code":"101","customer_id":"5b0e9b23e423b0d33c9f7ddfd", "purchases": "13", "last_activity_ts": "123523465"}

我正在尝试从包含错误代码101的字符串中提取客户ID，并包含以下代码：

select regexp_extract(field, '\"customer_id":"(.*)', 0) from table_name
where field rlike '"error_code":"101"'

但这给了我以下结果：

"customer_id":"5b0e9b23e423b0d33c9f7ddfd", "purchases": "13", "last_activity_ts": "123523465"}

预期结果：

5b0e9b23e423b0d33c9f7ddfd

你能帮我这个忙吗？

阅读 184

2021-07-01

共1个答案

admin

您可以使用以下正则表达式：

"customer_id":"([\w\d]+)"

演示：https :
//regex101.com/r/MEOGw8/1

测试：

{"param":"success","value":"10","level":"0","error_code":"101","customer_id":"5b0e9b23e423b0d33c9f7ddfd", "purchases": "13", "last_activity_ts": "123523465"}

比赛：

Match 1
Full match  63-104  `"customer_id":"5b0e9b23e423b0d33c9f7ddfd"`
Group 1.    78-103  `5b0e9b23e423b0d33c9f7ddfd`

SQL语句：

select regexp_extract(field, '"customer_id":"([\w\d]+)"',1, 1) from table_name
where field rlike '"error_code":"101"'

2021-07-01