sqlgoogle-bigquerybigquery-udf

Bigquery - Data Validation in custom field


I have data in below format. I need to run a rule as below - If column A contains value 15, it should be flagged as Valid, else it should be flagged as Invalid

Below is my Input and expected Output data

Input Data

Key | Value     
----|-----------
1221|10,15,23,33
1123|10,11,12
1990|19,15
1000|null

Below is my expected output

Key | Value     | Validation Result|
----|-----------|------------------|
1221|10,15,23,33|             Valid|
1123|10,11,12   |           Invalid|
1990|19,15      |             Valid|
1000|null       |           Invalid|
------------------------------------

I did try something with the split function, but it just splits into multiple rows, looking for something without splitting


Solution

  • Use below approach

    select *,
      if('15' in unnest(split(value)), 'valid', 'invalid') IsValid
    from your_table
    

    if applied to sample data in your question - output is

    enter image description here