SQL CONCAT returning null

[shelms]

Has anyone else experienced this problem? I'm attempting to SQL concat two fields and if the second field is null, the entire string appears as null. The documentation is unclear on the expected outcome, and contrary to how concat_ws operates.

SELECT
  substr(users.first, 0, 1),
  users.first,
  users.last,
  CONCAT(
    substr(users.first, 0, 1),
    ' ',
    users.last
  ) as abbr_name
FROM
  users

Here is an example of what I receive. 

Does anyone have any suggestions on how to get the results I've shown abbr_name without being entirely null if there is no last name?

Thanks,

Steve

[User16752245772 · Accepted solution]

Hi @Steve Helms​ ,

There is drawback in SQL Concatenate function, as a workaround you can use COALESCE , COALESCE converts null values with empty string by wrapping in it .

Like this :

----

SELECT
substr(user.first, 0, 1),
user.first,
user.last,
CONCAT(COALESCE(substr(user.first,0,1),'')," ",COALESCE(`last`,'')) as abbr_name
FROM
user

-----

[BilalAslamDbrx]

CONCAT is a function defined in the SQL standard and available across a wide variety of DBMS. With the exception of Oracle which uses VARCHAR2 semantic across the board, the function returns NULL on NULL input.

CONCAT_WS() is not standard and is mostly popularized by Microsoft SQL Server, and MySQL. Both ignore NULL semantic.

So Databricks is merely following precedent in both cases.