8

We would like to run a query that returns no of character in the given string if i have a string say : Baitul Sharaf, 105 Hill Road, Bandra (west), Mumbai I want to count no of ',' in the above string (In above case 3)

see the reference for my question Count the number of occurrences of a character in a string in Javascript I want to achieve the same in BigQuery

Community
  • 1
  • 1
Ravindra
  • 1,741
  • 3
  • 15
  • 19

2 Answers2

21

SELECT LENGTH(col) - LENGTH(REGEXP_REPLACE(col, ',', '')) FROM TableName

Ravindra
  • 1,741
  • 3
  • 15
  • 19
  • 2
    Works perfectly! SELECT LENGTH(col) - LENGTH(REGEXP_REPLACE(col, ',', '')) FROM (SELECT "Baitul Sharaf, 105 Hill Road, Bandra (west), Mumbai" col) – Felipe Hoffa May 12 '14 at 17:41
  • 2
    It works perfectly when string which occurrences you are counting has size that is equls to 1. If not then you need also divide by length of the string – angry_gopher Oct 17 '17 at 14:56
-1
select if(instr(x, ',') > 0, array_length(split(x, ',')) - 1, 0)
from (select 'Baitul Sharaf, 105 Hill Road, Bandra (west), Mumbai' x)

This solution doesn't assume the length of the string to be counted. It works fine in the following situation as well.

select if(instr(x, 'ba') > 0, array_length(split(x, 'ba')) - 1, 0)
from (select 'babababa' x)
ronencozen
  • 1,817
  • 1
  • 12
  • 18
  • Can you provide an example of how `instr` solves this question? `instr`provides a position in the string, not a count of occurrences. – Steven Ensslen Aug 30 '21 at 02:03