I am struggling with this simple regex. I have a column of type Variant which looks like:

[ { "AccountCode": "20240", "Description": "Hardware", "ItemCode": "Platform", "LineAmount": ...

I am trying to extract all of the ItemCodes as a new column. In this case it is "Platform" and it could be alphanumeric in other cases. I know I need REGEXP_SUBSTR but new to regex. Any advice?

CodePudding user response：

You don’t need to use REGEX. Snowflake allows you to query the contents of json data in a variant column directly. This is documented here