cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL CONCAT returning null

shelms
New Contributor II

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.Screen Shot 2022-03-14 at 4.00.53 PM 

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

1 ACCEPTED SOLUTION

Accepted Solutions

User16752245772
Contributor

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

-----

image

View solution in original post

3 REPLIES 3

User16752245772
Contributor

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

-----

image

BilalAslamDbrx
Honored Contributor III
Honored Contributor III

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.

Kaniz_Fatma
Community Manager
Community Manager

Hi @Steve Helms​ , Would you like to share with us whether you got your answer, or else do you require more help? Would you like to mark the best answer in case your problem is resolved?

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!