CODEWITHSUNDEEP
sqlgoogle-bigquery
HAIDER TAYYAB
HAIDER TAYYAB

Reputation: 47

Get Position of a String in a field with delimiters BigQuery

I want to get the position of a word in a field that has the following data with the delimiter as "->":

Example:

Row 1| "ACT -> BAT -> CAT -> DATE -> EAT"

Row 2| "CAT -> ACT -> EAT -> BAT -> DATE"

I would like to lets say extract the position of CAT in each row.

Output would be -

Row 1| 3

Row 2| 1

Ive tried regex_instr and instr but they both return position of the alphabet i think not the word

Upvotes: 1

Views: 761

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Consider below

select *, 
  array_length(split(regexp_extract(col, r'(.*?)CAT'), '->')) as position
from your_table

if applied to sample data in your question - output is

enter image description here

Upvotes: 1

Related Questions