Concatenate With String But Exclude When Null

Concatenate with string but exclude when null

Something like this... I added simulated inputs to test all four possibilities. However, note that if you may have last name but no first name, and also first name but no last name, the combined column will show just one name but you will not know if it is first or last. (Also, when a comma is added, I also add a space after the comma, as is usual; that can be controlled easily though.)

with
  tablea ( lastname, firstname, id ) as (
               select 'Smith', 'Ann'  , 1 from dual
     union all select null   , null   , 2 from dual
     union all select 'Ang'  , null   , 3 from dual
     union all select null   , 'Murat', 4 from dual
  )
-- End of simulated inputs (for testing only, not part of the solution).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select lastname, firstname, id,
       lastname 
         || case when lastname is not null and firstname is not null then ', ' end
         || firstname
       as last_first
from   tablea
;



LASTNAME   FIRSTNAME          ID LAST_FIRST 
---------- ---------- ---------- ------------
Smith      Ann                 1 Smith, Ann  
                               2             
Ang                            3 Ang         
           Murat               4 Murat

SQL Server concatenate ignore null value

The STUFF method is probably the better option here:

STUFF(CONCAT(',' + NULLIF(@item1, ''),',' + NULLIF(@item2, ''),',' + NULLIF(@item3, '')),1,1,'')

Concatenate with NULL values in SQL

Use the COALESCE function to replace NULL values with an empty string.

SELECT Column1 + COALESCE(Column2, '') AS Result
    FROM YourTable

Concatenate columns in t-sql but exclude columns that are null

In SQL Server, concatenating null to a string will result with null. Taking advantage of that fact, you can do something like this:

SELECT ISNULL('Column 1: ' + t.Column1, '') + 
       ISNULL('Column 2: ' + t.Column2, '') + 
       ISNULL('Column 3: ' + t.Column3, '') 
FROM Table

In SQL Server 2012 or higher, you can use the Concat built in function, but you still need to concatenate the columns to their hard coded description the old fashion way to take advantage of the effect described before.

SELECT CONCAT('Column 1: ' + t.Column1, 
              'Column 2: ' + t.Column2, 
              'Column 3: ' + t.Column3) 
FROM Table

Exclude Null Entries when concatenate

The CONCAT() function solves your problem:

Select CONCAT(Col1, ', ', Col2, ', ', Col3)

It ignores NULL values.

The most recent versions of SQL Server support CONCAT_WS():

CONCAT_WS(', ', col1, col2, col3)

This produces slightly different results. For instance, if the second two values are NULL, the first returns 'A,,' but the second returns 'A'. I'm not sure which you really want.

SQL using If Not Null on a Concatenation

Here would be my suggestions:

PostgreSQL and other SQL databases where 'a' || NULL IS NULL, then use COALESCE:

SELECT firstname || COALESCE('-' || middlename, '') || '-' || surname ...

Oracle and other SQL databases where 'a' || NULL = 'a':

SELECT first name || DECODE(middlename, NULL, '', '-' || middlename) || '-' || surname...

I like to go for conciseness. Here it is not very interesting to any maintenance programmer whether the middle name is empty or not. CASE switches are perfectly fine, but they are bulky. I'd like to avoid repeating the same column name ("middle name") where possible.

As @Prdp noted, the answer is RDBMS-specific. What is specific is whether the server treats a zero-length string as being equivalent to NULL, which determines whether concatenating a NULL yields a NULL or not.

Generally COALESCE is most concise for PostgreSQL-style empty string handling, and DECODE (*VALUE*, NULL, ''... for Oracle-style empty string handling.

String concatenation with a null seems to nullify the entire string - is that desired behavior in Postgres?

It's not a bug and it's not "weird".

The SQL standard requires any expression that involves null yields null. This is not limited to string concatenation, it also applies to computations e.g.: 42 * null returns null.

This also applies to comparisons: 42 > null yields null. So the comparison it's neither true nor false. Although in reality this has the effect of "false", but more because it's "not true", rather then false. But negating such an expression yields null again, not "true".

Because null is so special, the only way to check if something is null is to use the operator IS NULL or IS NOT NULL. x is null yields either true or false, it never yields null, so expressions using the is null or is not null operator never return null - so this is an exception to my statement above (thanks Jonathan for pointing that out).

---

Another - maybe surprising - fact about null values is how they are handled by aggregate functions. While the expression 4 + 5 + null yields null, the sum() over those (column) values would yield 9, because aggregates ignore null values.

Given the following table:

    col1
--------
       1
       2
       3 
     null

sum(col1) will return 6, and avg(col1) will return 2 (sum = 6, number of elements added: 3)

SQL Server : concatenate values and ignore null or blank values

Building on Prdp's answer, a simple replace (or two) can eliminate the double/triple spaces.

Declare @YourTable table ([st number] varchar(25),Prefix varchar(25),[st name] varchar(25),suffix varchar(25),Dir varchar(25),unit varchar(25),number varchar(25),city varchar(25),zip varchar(25))
Insert Into @YourTable values
('1234','W' ,'Main'  ,'St' ,NULL,'Unit','34','LA','90210'),
('345' ,NULL,'George','Ave',NULL,NULL  ,NULL,'SF','94525'),
('123' ,'E' ,'Bloom' ,'Ct' ,'W' ,NULL  ,NULL,'DC','99342')

Select FullAddress = replace(replace(concat([st number],' ',Prefix,' ',[st name],' ',suffix,' ',Dir,' ',unit,' ',number,' ',city,' ',zip),'   ',' '),'  ',' ')
 From  @YourTable A

Returns

FullAddress
1234 W Main St Unit 34 LA 90210
345 George Ave SF 94525
123 E Bloom Ct W DC 99342

Concatenate Multiple Strings While Ignoring Null Values

There are, of course, multiple ways that this can be achieved, and since you didn't provide any code or examples of how you are attempting to do this, I will provide two options.

1 - You can concatenate the values using a combination of the & and + operators.

For example, let's say you have 15 checkboxes, all named similarly like chkState01, chkState02 ... through chkState15. And for the simplicity of my sample code, let's assume that when referencing the checkbox control directly in code as chkState01 that it will return either the 2 letter string abbreviation of the State it represents (i.e. NY) if the checkbox was checked, or it will return Null if the checkbox was not checked. With that, you could get your results in 2 ways:

Option A

StateList = (chkState01 + ",") & (chkState02 + ",") & (chkState03 + ",") ....

If those 3 check boxes returned the following values

chkState01 = "NY"
chkState02 = Null
chkState03 = "FL"

Then the result of that concatenation code would be:

NY,FL,

Notice that the string ends with an extra comma (,) and always would since you can't know ahead of time how many of the checkboxes will be checked. You would then need to trim that comma from your list before using it most likely.

Option B

'Create the list with a trailing comma that will need removed
Dim x as Integer
For x = 1 to 15
    StateList = StateList & (Me("chkState" & Format(x, "00")) + ",")
Next x

or, you could do:

'Create the list without a trailing comma
Dim x as Integer
For x = 1 to 15
    If Not IsNull(Me("chkState" & Format(x, "00"))) Then
        If Len(StateList) > 0 Then
            StateList = StateList & "," & Me("chkState" & Format(x, "00"))
        Else
            StateList = Me("chkState" & Format(x, "00"))
        End If
    End If
Next x

Notice that you can reference a control on a form by "generating" the name of that control as a string and referencing it in the Me("yourcontrolname") format. This is one advantage to naming controls that are similar in a fashion that lends itself to a looping structure like this. The Format command formats the number returned by x as a 2 digit with leading zeros i.e. 1 becomes 01

Further, using & to concatenate two items, where at least one of them is a string, will always result in a string output (Null & "S" = "S"). However, using the + to concatenate two items, where at least one of them is a Null, will always result in Null output (Null + "S" = Null). Hence the checkboxes where the value returns Null does not cause additional commas to be included in the result.

2 - You can write more complicated code to dynamically loop through the checkboxes and build the output list.

More likely, you are going to need to use additional code to determine which checkbox is which state abbreviation and to return the correct string value. Maybe you made the state abbreviation part of the checkbox name i.e. chkState_NY, chkState_FL or maybe you have put the abbreviation in the Tag property of each checkbox.

Let's say you used special control naming chkState_NY, chkState_FL. You could do the following:

Dim ctl as Access.Control

For Each ctl in Me.Controls
    If ctl.Name Like "chkState_??" Then
        If ctl.Value = True Then
            If Len(StateList) > 0 Then
                StateList = StateList & "," & Right(ctl.Name,2)
            Else
                StateList = Right(ctl.Name,2)
            End If
        End If
    End If
Next ctl

---

Related Topics