How to avoid space while concatenation if database values are null?

sql server concatenate columns if not null
sql concatenate columns if not null
sql concatenate columns with null values
concat when not null
concat null values in hive
coalesce
mysql concat null
bigquery concat null

I am concatenating three database values Firstname,middlename,lastname.Here is the Query -

Select ISNULL(t.FirstName,'')+' '+ISNULL(t.MiddleName,'')+' '+ ISNULL(t.LastName,'') as [UserName],Email from table1 t

I am getting null values for some middle names. Thats why I am getting extra spaces after concatenation.

Firstname LastName --getting two spaces at place of one

I want if middle name is null then only one space will be present in result.If it not null then there is one space between firstname, middlename and lastname. I have tried some methods to avoid this but nothing worked.

Put the space inside ISNULL function:

Select ISNULL(t.FirstName + ' ', '') 
    + ISNULL(t.MiddleName + ' ', '')
    + ISNULL(t.LastName,'') as [UserName]

How to ignore Null fields when concatenating, I use D3 as a back end via ODBC and find that if a field is null then CR spits the dummy and doesn't show any data at all, even with the option to convert null  The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

you can use replace() function to replace double spaces to single space

Select replace(concat(coalesce(t.FirstName,''), ' '
    , coalesce(t.MiddleName,' ')
    , coalesce(t.LastName,''), '  ', ' ')) as [UserName]
    , Email 
from table1 t

How to avoid technically the null values while concatenating columns, Can you provide some sample data highlighting the problem? Instead of 'passing spaces', what's wrong with an empty string? If the answer to  Given the technique of concatenating a series of ISNULL functions whose parameters are all strings or casted or converted into strings, it's not that difficult to eliminate a null value from

you can make use of case when clause

SELECT ISNULL(t.FirstName,'') + 
  CASE WHEN t.MiddleName IS NOT NULL THEN
    ' ' + t.MiddleName + ' '
  ELSE 
    ' ' 
  END +
  ISNULL(t.LastName,'') as [UserName],
  Email
FROM table1 t

Example here

New Perspectives on Microsoft Access 2013, Introductory, If a field in a record does not contain any information at all, it has a null value. A field in a record that contains any data at all—even a single space—is nonnull. field values and null Parent field values in the tblPatient table, while records for children The & (ampersand) operator is a concatenation operator that joins text​  CONCAT is the new T-SQL function introduced in SQL Server 2012. It is used to concatenate the values. It accepts many parameter values seperated by comma. All parameter values are concatenated to a single string. A simple example is. SELECT CONCAT ('SQL Server', ' 2012') which results to SQL Server 2012. The same can be done using + in the

New Perspectives on Microsoft Access 2013, Comprehensive, If a field in a record does not contain any information at all, it has a null value. A field in a record that contains any data at all—even a single space—is nonnull. field values and null Parent field values in the tblPatient table, while records for children The & (ampersand) operator is a concatenation operator that joins text​  A NULL is less space than "No value provided" or even a shorter string like a blank space if you are dealing with a high volume database. On that topic of an empty string, I don't like that implementation because as stated earlier, there is a distinction between it being sent that way vs. it was truly sent as NULL and if you can't tell the

12.5 String Functions, LTRIM(), Remove leading spaces Returns the numeric value of the leftmost character of the string str . CONCAT() returns NULL if any argument is NULL . LOWER() (and UPPER() ) are ineffective when applied to binary strings produce a result that can be used as a properly escaped data value in an SQL statement. We can use SQL ISNULL function with + operator to replace NULL values with a space or any specific value. Execute the following query and we can still use SQL Plus (+) operator to concatenate string for us. SELECT TOP 10 FirstName,

|| (Concatenation) operator - Amazon Redshift, For both the CONCAT function and the concatenation operator, if one or both strings is null, the result of the concatenation is null. Syntax. string1 || string2  If you’ve ever tried to concatenate a string with a number while using SQL Server, but received an error, this article should clear things up for you.There’s more than one way to perform concatenation using T-SQL in SQL Server, and if you’re concatenating different data types (like a string and a number) then you may receive an error, depending on how you do the concatenation.

Comments
  • nice, never thought of concatenating space with column value inside ISNULL.
  • Thanks for you help. But this is not working properly. when middle name is null it add space with null value.
  • @NeerajKumar, when t.MiddleName is null, ISNULL(t.MiddleName + ' ', '') results ''. Have you checked it?
  • @DenisRubashkin yes i Checked it.@Metal's Solution worked for me.
  • @NeerajKumar how is it that your code adds null with space? anything with null equals to null. (null + ' ' = null). this should work. here is the link rextester.com/PEZ63120
  • Thanks for your help. It works for me.
  • @NeerajKumar, great! please don't forget to mark this answered.