Extract a substring and take second value in a Bigquery Column

Ririn 0
Viewed 34

I have this data:

id  val
1   ajkdks - jkdj
2   djs - djsd

I want to take only the second value. Which is:

id val
1  jkdj
2  djsd

I know the query if using MySQL:

SUBSTRING_INDEX(SUBSTRING_INDEX(val, " - ", 2)," - ",-1)

But what the query if i using bigquery?

google-bigquery
2 Answers

Use below

select id, split(val, ' - ')[safe_offset(1)] val
from your_table

if applied to sample data in your question - output is

enter image description here

Mikhail Berlyant0

We could phrase this using REGEXP_EXTRACT:

SELECT id, REGEXP_EXTRACT(val, r'[^ -]+$') AS val
FROM yourTable
ORDER BY id;

Note that the above regex approach is also robust to the case where val might not have any hyphen separator, in which case the entire value would be returned.

Tim Biegeleisen0
Related