format the timestamp output for TABLE_DATE_RANGE() BigQuery

Question

I have a requirement to query different tables a once to save my time. Tables names like

abc_yyyymmdd

can be easily query using the

table_date_range(abc_,timestamp('2016-01-01'),timestamp('2016-03-12'))

but I have different format table name

abc_mm_dd_yyyy

is there a way to query in these tables using table_date_range.

Answer 1

In Legacy SQL you can use TABLE_QUERY for this

So it can be something like below

SELECT *  
FROM (
  TABLE_QUERY(YourDataset, 'LEFT(table_id, 4) = "abc_" AND LENGTH(table_id) = 14
  AND CONCAT(SUBSTR(table_id,11,4),'-',SUBSTR(table_id,5,2), -",SUBSTR(table_id,8,2)) 
    BETWEEN "2016-01-01" AND "2016-03-12"')
)  

Answer 2

If you can use Standard SQL, you can use the _TABLE_SUFFIX pseudo column to work with any table name format.

Is there an equivalent of table wildcard functions in BigQuery with standard SQL?

In this case, it would be something like:

SELECT ... FROM `mydataset.abc_2016_*` WHERE _TABLE_SUFFIX = '01-01' or _TABLE_SUFFIX = '03-12'