按模式划分的雪花子串

Question

  accountNumber:123456
 {"accountNumber":"123456"}

我需要一个动态的方式只提取123456从这些字符串。你能提出一个解决办法吗。

Answer 1

使用regexp_substr(…)内置函数使用正则表达式模式提取子字符串。

如果每个列值中只有一个数字，那么数字模式或数字字符范围语法就足够了：

SELECT
'accountNumber:123456' i1,
regexp_substr(i1, '[0-9]+') r1,
'{"accountNumber":"123456"}' i2,
regexp_substr(i2, '[0-9]+') r2;

+----------------------+--------+----------------------------+--------+         
| I1                   | R1     | I2                         | R2     |
|----------------------+--------+----------------------------+--------|
| accountNumber:123456 | 123456 | {"accountNumber":"123456"} | 123456 |
+----------------------+--------+----------------------------+--------+

如果数字正好是6位数宽，请使用{n}重复语法：

select
'accountNumber:123456,anotherNumber:123' i1,
regexp_substr(i1, '[0-9]{6}') r1,
'{"accountNumber":"123456", "anotherNumber": 123}' i2,
regexp_substr(i2,'[0-9]{6}') r2;

+----------------------------------------+--------+--------------------------------------------------+--------+
| I1                                     | R1     | I2                                               | R2     |
|----------------------------------------+--------+--------------------------------------------------+--------|
| accountNumber:123456,anotherNumber:123 | 123456 | {"accountNumber":"123456", "anotherNumber": 123} | 123456 |
+----------------------------------------+--------+--------------------------------------------------+--------+

select
'accountNumber:123456,anotherNumber:123,somethingElse:456789' i1,
regexp_substr(i1, 'accountNumber[:" ]+([0-9]{6})', 1, 1, 'e', 1) r1,
'{"accountNumber":"123456", "anotherNumber": 123, "somethingElse": 456789}' i2,
regexp_substr(i2, 'accountNumber[:" ]+([0-9]{6})', 1, 1, 'e', 1) r2;

+-------------------------------------------------------------+--------+---------------------------------------------------------------------------+--------+
| I1                                                          | R1     | I2                                                                        | R2     |
|-------------------------------------------------------------+--------+---------------------------------------------------------------------------+--------|
| accountNumber:123456,anotherNumber:123,somethingElse:456789 | 123456 | {"accountNumber":"123456", "anotherNumber": 123, "somethingElse": 456789} | 123456 |
+-------------------------------------------------------------+--------+---------------------------------------------------------------------------+--------+

select
parse_json('{"accountNumber":"123456", "anotherNumber": 123, "somethingElse": 456789}'):accountNumber::integer account_number;

+----------------+                                                              
| ACCOUNT_NUMBER |
|----------------|
|         123456 |
+----------------+