Bigquery : combine two columns

Question

I am just wondering how can we combine 2 fields in Bigquery.

This is my SQL

SELECT
 cast(tbl.table_name as string) + '.' + cast(col.column_name as string)
FROM 
xy.INFORMATION_SCHEMA.TABLES tbl
INNER JOIN 
xy.INFORMATION_SCHEMA.COLUMNS col
ON tbl.table_name = col.table_name

This is the error I'm getting

No matching signature for operator + for argument types: STRING, STRING. Supported signatures: INT64 + INT64; FLOAT64 + FLOAT64; NUMERIC + NUMERIC at [3:2]

Answer 1

For BigQuery Standard SQL

You can just use CONCAT() function without CAST'ing string to string as in below example

CONCAT(tbl.table_name, '.', col.column_name)     

Yet another option is FORMAT() function

FORMAT('%s.%s', tbl.table_name, col.column_name)   

Answer 2

You can use concat as defined in this link

This is the SQL

SELECT
 CONCAT(cast(tbl.table_name as string), '.',cast(col.column_name as string))
FROM 
`dataset.INFORMATION_SCHEMA.TABLES` tbl
INNER JOIN 
`dataset.INFORMATION_SCHEMA.COLUMNS` col
ON tbl.table_name = col.table_name

Output

+---------------------+
| fo_                 |
+---------------------+
| Table1.col          |
| Table1.col2         |
+---------------------+