Using REGEXP_CONTAINS with a list of substrings

I want to know if its possible to use REGEXP_CONTAINS with a list of substrings from a column in another table.

Essentially I will have one table containing a list of substrings that I want to check against and I am using those substrings against the whole set of table names in a dataset.

Below is the closest approximation as to how I thought it can be done.

SELECT REGEXP_REPLACE(table_name, '^RESULT_', '')
AS table_names
FROM projects.dataset.INFORMATION_SCHEMA.TABLES
WHERE REGEXP_CONTAINS(table_name, '[0-9]')
AND REGEXP_CONTAINS(table_name, (
    SELECT SubStrings
    AS strings
    FROM project.dataset.SubStringTable))
ORDER BY table_name

Unfortunately this method just hits me with an error saying that the selection from the sub string table returned too many arguments and so regexp_contains couldnt use that as an input argument.

Would using a while loop in the selection of the substring in the table be a solution to this? E.G.

While i <= length(SubStringTable)
SELECT SubStrings(i)
AS string
FROM project.dataset.SubStringTable

Answer

Use below

SELECT REGEXP_REPLACE(table_name, '^RESULT_', '') AS table_names
FROM projects.dataset.INFORMATION_SCHEMA.TABLES
WHERE REGEXP_CONTAINS(table_name, '[0-9]')
AND ( 
  SELECT LOGICAL_OR(REGEXP_CONTAINS(table_name, SubStrings))
  FROM `project.dataset.SubStringTable`
)

Advertisement

Answer