Bigquery - Data Validation in custom field-CodePudding

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

CodePudding user response：

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