CODEWITHSUNDEEP
google-bigquerysubstringdelimiter
Taylor
Taylor

Reputation: 31

How can I parse out of the last instance of text after a delimiter (big query)

Ex of value: [{‘test’:’https://‘,’id’,’2521’,’Shipment’}]

I would only want to return Shipment as the value returned for that field

Upvotes: 0

Views: 1310

Answers (3)

Valentin
Valentin

Reputation: 365

I would avoid unnecessary arrays and use substr/insrt whenever possible

select substr(components, instr(components, "’", -1, 2) + 1, instr(components, "’", -1, 1) - instr(components, "’", -1, 2) - 1)
from (select "[{‘test’:’https://‘,’id’,’2521’,’Shipment’}]" components)

Upvotes: 0

Taylor
Taylor

Reputation: 31

Solution:

SPLIT(components, "'")[SAFE_OFFSET(ARRAY_LENGTH(SPLIT(components, "'")) - 2)]

Upvotes: 3

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Use below instead

array_reverse(split(translate(components, '[]{}', '')))[offset(0)]

Upvotes: 1

Related Questions