Skip to content
Advertisement

Need to find string using bigquery

We have below string column and having below data

enter image description here

and I want to find Null count present in string columns means how many times null value('') present in front of id column present in select statement using big query.

Don’t use string position.

Expected output:

count of null ('')id =3
1st row,2nd row and 5th row

Advertisement

Answer

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  FORMAT(
    "count of null ('')id = %d. List of id is: %s", 
    COUNT(*),  
    STRING_AGG(CAST(ID AS STRING))
  ) AS output
FROM `project.dataset.table`
WHERE REGEXP_CONTAINS(String, r"(?i)''s+(?:as|)s+(?:id|[id])")           

if to apply to sample data from your question – the output is

Row output   
1   count of null ('')id = 3. List of id is: 1,2,5   
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement