Extracting data from string - SQL

Question

In BigQuery, I have a table with a column that produces long string values similar to the one provided below. There are two main parts of the string: cust_no and comp_no. Each part contains a "value" and "updated_at_ms". I am trying to extract these both cust_no and comp_no with their "value" as two new columns. "Updated_at_ms" is not necessary.

{"$cust_no": {"value": "90164F59-1120-4F2B-811D-7FEDE3CEF701", "updated_at_ms": 1600301818327}, "$comp_no": {"value": "1548715734691-5404642", "updated_at_ms": 1600301818327}}

Anyone know how I can do this extraction? Any help is appreciated. Thank you in advance.

Sergey Geron · Accepted Answer · 2021-05-18 08:43:35Z

4

Use json_value:

with mytable as (
  select '{"$cust_no": {"value": "90164F59-1120-4F2B-811D-7FEDE3CEF701", "updated_at_ms": 1600301818327}, "$comp_no": {"value": "1548715734691-5404642", "updated_at_ms": 1600301818327}}' as col
)
select
  json_value(col, '$."$cust_no".value'),
  json_value(col, '$."$comp_no".value'),
from mytable

answered May 18, 2021 at 8:43

Sergey Geron

10.5k4 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sabasabo · Accepted Answer · 2021-05-18 08:18:13Z

1

Sorry for not having a full answer.

If I understand correctly, you want to take "the good parts" from a single column.

A direction would be to use patindex and maybe substring.

SELECT SUBSTRING(MyCol, (PATINDEX('%"$cust_no": {"value": "%',[MyCol])),length)

Where length is computed with PATINDEX of ", "updated_at_ms" or something like that

Example from here:SQL Server String extract based on pattern

substring API: SUBSTRING(string, start, length)

patIndex API: PATINDEX(%pattern%, string)

Direction for computing length: substring of variable length

answered May 18, 2021 at 8:18

sabasabo

1196 bronze badges

Collectives™ on Stack Overflow

Extracting data from string - SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related