Bigquery Wildcard characters

Question

I have two tables say SAMPLE_CODE, and SAMPLE_CODE_TYPE along with several tables. There are 21 rows in SAMPLE_CODE and 4 rows in SAMPLE_CODE_TYPE. Both table has similar column say code_type STRING. Other than that all column schema are different. When I query

SELECT * FROM `MYPROJECT.MYDATASET.SAMPLE* WHERE _TABLE_SUFFIX = '_CODE'

-- Here I have hardcoded the '_CODE' value but actually this value comes from another subquery. But the one I have hardcoded is not giving the desired output.
-- I want this query to select SAMPLE_CODE table but the output of this query is not so. It prints 21 rows of the SAMPLE_CODE_TYPE while there are only 4 rows in that table and 21 rows in SAMPLE_CODE table. Is it kind of mix matching between two tables? How is this possible ? Can any one tell me what should I do to the query so that it fetches the values of the SAMPLE_CODE table based on _TABLE_SUFFIX value. what am I missing here?

Thankyou

Answer 1

Use the LIKE operator:

SELECT *
FROM `MYPROJECT.MYDATASET.SAMPLE`
WHERE `_TABLE_SUFFIX` LIKE '%\\_CODE%';