CODEWITHSUNDEEP
sqlsql-servert-sqlisnull
wonderful world
wonderful world

Reputation: 11599

Function to check for NULL and concatenating the strings

I have a SQL statement that concatenates First Name and Last Name of the person. I use the following SQL statement.

ISNULL(FullName.FirstName, '') + ' ' + ISNULL(FullName.LastName, '')

What I need is to change this to add the FirstName and LastName only if they are not both NULL. If one of them is not NULL, use that value.

Examples:

  1. FirstName and LastName are NULL -> NULL
  2. FirstName is John and LastName is NULL -> John
  3. FirstName is NULL and LastName is Doe -> Doe

-> indicates the result

Upvotes: 0

Views: 322

Answers (4)

Charlieface
Charlieface

Reputation: 71579

A better SQL Server 2017 solution:

Use the new CONCAT_WS function, which concatenates with a separator, ignoring nulls

CREATE TABLE #emp (ID INT IDENTITY(1,1), FirstName VARCHAR(25), LastName VARCHAR(25));
    
INSERT INTO #emp 
VALUES(null,null)
,('John',null)
,('Doe',null)
,('John', 'Doe');

SELECT FullName = CONCAT_WS(' ', FirstName, LastName)
    ,*
FROM #emp;

db<>fiddle

Upvotes: 1

Stephan
Stephan

Reputation: 6018

  • Use CONCAT() to concatenate without any NULL issues
  • TRIM() to remove any spaces for when one or both fields are missing
  • NULLIF() to replace '' with NULL if first and last name are both missing

SQL Server 2017+ Solution

DROP TABLE IF EXISTS #emp
CREATE TABLE #emp (ID INT IDENTITY(1,1), FirstName VARCHAR(25), LastName VARCHAR(25));
    
INSERT INTO #emp 
VALUES(null,null)
,('John',null)
,('Doe',null)
,('John', 'Doe');

SELECT FullName = NULLIF(TRIM(CONCAT(FirstName,' ',LastName)),'')
    ,*
FROM #emp

Upvotes: 1

PraveenP
PraveenP

Reputation: 796

You can use simple case statement to solve this:

    create table emp (emp_id int4, fname varchar(10), lname varchar(10));
    
    insert into emp (emp_id) values(1);
    insert into emp (emp_id,fname) values(2, 'John');
    insert into emp (emp_id,lname) values(3, 'Doe');
    insert into emp (emp_id,fname,lname) values(4, 'John', 'Doe');
    
    
    select emp_id,fname, lname, 
    case 
        WHEN FNAME is not null and LNAME is not null then 
            FNAME||' '||LNAME
        WHEN FNAME is not null and LNAME is NULL then 
            FNAME
        WHEN FNAME is null and LNAME is not NULL then 
            LNAME
        else null
        end as FULL_NAME
    from emp;

enter image description here

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521269

Actually if you just wrap you current expression inside TRIM() it should give you the behavior you want:

SELECT TRIM(ISNULL(FullName.FirstName, '') + ' ' +
            ISNULL(FullName.LastName, '')) AS FullName
FROM yourTable;

In the event that only the first or last name be present, your original expression would leave a dangling leading/trailing space. The call to TRIM() fixes that by removing this extra space.

Upvotes: 1

Related Questions