BigQuery - Concatenating String+EmptyString results in NULL value

Question

I have two string type fields "Source" and "Detail" with either of this having null value. Now I'm concatenating both the string fields separated by hyphen(-); but due to NULL value, the output is also NULL.

Refer image consisting of the actualoutput and outputexpected (highlighted yellow).

Can someone please suggest how to get the output expected?

Query I used:

SELECT Source, Detail, CONCAT(cast(Source as string), "-", cast(Detail as string)) AS actualoutput FROM tablename

Mikhail Berlyant · Accepted Answer · 2020-01-22 04:49:20Z

7

SELECT Source, Detail, CONCAT(IFNULL(Source, ''), "-", IFNULL(Detail, '')) AS actualoutput 
FROM tablename

Note: as both "Source" and "Detail" are already of type STRING - you can omit CAST'ing to STRING

answered Jan 22, 2020 at 4:49

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2020-01-22 04:48:59Z

4

Use COALESCE to replace NULL with empty string:

SELECT
    Source,
    Detail,
    CONCAT(COALESCE(Source, ''), '-', COALESCE(Detail, '')) AS actualoutput
FROM tablename;

Note that anything concatenated with NULL will always yield NULL.

answered Jan 22, 2020 at 4:48

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

BigQuery - Concatenating String+EmptyString results in NULL value

2 Answers 2

Comments

Comments

Your Answer

Linked

Collectives™ on Stack Overflow

BigQuery - Concatenating String+EmptyString results in NULL value

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related