[解決済み] パターンによる雪片の部分文字列

質問

テーブルの中に以下のような文字列のカラムがあります。

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

これらの文字列から123456のみを抽出する動的な方法が必要です。解決策を提案してもらえますか？

どのように解決するのですか？

使用方法 その REGEXP_SUBSTR(…) 組み込み関数 を使い、正規表現パターンで部分文字列を抽出します。

各列の値に数字が1つしか入らない場合は 数値パターン または 数値文字範囲 の構文で十分です。

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 |
+----------------------------------------+--------+--------------------------------------------------+--------+

番号がテキストにのみ続く必要がある場合 accountNumber を導入することができます。 (キャプチャグループ) :

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 |
+-------------------------------------------------------------+--------+---------------------------------------------------------------------------+--------+

完全に正しい正規表現を構築するには、データで起こりうるすべての差異について、より多くの知識が必要になります。以下のようなサイトで、良いテストセットを使って対話的にパターンを構築してみてください。 Regex101 , RegExr などがあり、開発がしやすくなっています。

備考 : データがJSON形式である場合、Snowflakeは以下のことを許可します。 にパースします。 VARIANT データ型 から より自然に問い合わせることができる :

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

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