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:
-> indicates the result
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
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.
NULL instead of an empty string? If FirstName is NULL and LastName is NULL, the result should return a NULL.
NULL when both first and last name be missing. Based on what you tell me, I can update my answer.NULL.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;
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;
