How to extract the value from the string in BigQuery-CodePudding

I have the following value as String in one column in bigquery table. I need to extract the content value using BigQuery view.

{type:System.Int32,content:202104}

I have converted the string like below and tried the JSON_EXTRACT syntax its working.

SELECT 
JSON_EXTRACT(JSON'{"type":"System.Int32","content":202104}', '$.content') 
AS json_data;

But I have converted this manually from

{type:System.Int32,content:202104}

{"type":"System.Int32","content":202104}

How can I achieve the same using query? Can anyone help me to resolve this query, Thanks in advance!

CodePudding user response：

This may help:

SELECT 
JSON_EXTRACT_SCALAR(REGEXP_REPLACE('{type:System.Int32,content:202104}', r'[^\:\,{}] ', r'"\0"'), '$.content')
AS json_data

CodePudding user response：

You may use REGEXP_EXTRACT to directly extract the value of the "content". Please refer to