Count the number of occurrence of a number in string of numbers using BigQuery

Question

I have a requirement where I want to count the number of times a particular number appeared in the string of numbers using Google Bigquery.

e.g

user_5221944 · Answer

Consider below approach

select raw_data, 
  ( select count(*)
    from unnest(split(raw_data)) el
    where el = '1'
  ) as appearances
from your_table

if applied to sample data in your question - output is

Another option would be

select raw_data, 
  array_length(regexp_extract_all(raw_data, r'\b1\b')) as appearances
from your_table

with same output, obviously