topic Re: SQL CONCAT returning null in Data Engineering

SQL CONCAT returning null

shelms — Mon, 14 Mar 2022 21:05:30 GMT

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

Re: SQL CONCAT returning null

User16752245772 — Tue, 15 Mar 2022 15:17:00 GMT

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

-----

Re: SQL CONCAT returning null

BilalAslamDbrx — Mon, 21 Mar 2022 10:37:14 GMT

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.