TransWikia.com

How do I concatenate only if not null in SQL Server?

Stack Overflow Asked on February 5, 2021

I have this table:

PersonTable

|  id  | name |
---------------
|  10  | Mike |
| NULL | Jane |

I want to select id and name from the table and use concat on id, but only if it’s not null, like this:

+------+------+
|  Id  | Name |
+------+------+
| A10  | Mike |
| NULL | Jane |
+------+------+

I’ve tried the following:

SELECT ISNULL(concat('A', id), NULL) AS id, name FROM PeronTable

But my query returns this:

+-----+------+
| Id  | Name |
+-----+------+
| A10 | Mike |
| A   | Jane |
+-----+------+

3 Answers

You could simply use NULLIF() as the following:

SELECT NULLIF(CONCAT('A', Id), 'A') Id,
       Name
FROM
(
  VALUES
  (10, 'Mike'),
  (NULL, 'Jane')
) T(Id, Name);

OR

SELECT TT.Value Id,
       T.Name
FROM
(
  VALUES
  (10, 'Mike'),
  (NULL, 'Jane')
) T(Id, Name) CROSS APPLY(VALUES (NULLIF(CONCAT('A', Id), 'A'))) TT(Value);

The function CONCAT() will returns 'A' if the Id column is NULL, thus you need just to check if it returns 'A' using NULLIF() function which will returns NULL if the concatenated string = A.

You could also use CASE expression if you which to as:

SELECT CASE WHEN TT.Value <> 'A' THEN Value END Id,
       T.Name
FROM
(
  VALUES
  (10, 'Mike'),
  (NULL, 'Jane')
) T(Id, Name) CROSS APPLY(VALUES (CONCAT('A', Id))) TT(Value);

Answered by Ilyes on February 5, 2021

You can try like the below code to get your expected solution:

Select 'A' + convert(varchar(255), id) AS id, name
from PeronTable;

Here is a sample screenshot :

enter image description here

Answered by Ankit Das on February 5, 2021

Hmmm. You can use + instead of concat():

select 'A' + convert(varchar(255), id), name
from t;

convert() (or cast()) is necessary assuming that id is a number and not a string.

+ returns NULL if any argument is NULL; concat() ignores NULL arguments.

Of course, you can use concat() with a case expression:

select (case when id is not null then concat('A', id) end), name
from t;

Answered by Gordon Linoff on February 5, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP