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

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         |
+---------------------+

Answer 2

you have two be careful with null values in one of them it may result in null for the concat, use ISNULL to check

CONCAT(IFNULL(col1, ''), "-", IFNULL(col2, ''))

see BigQuery - Concatenating String+EmptyString results in NULL value