13

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
  • 2,091
  • 3
  • 18
  • 21

1 Answers1

30

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

Ravindra
  • 2,091
  • 3
  • 18
  • 21
  • 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