CODEWITHSUNDEEP
sqlsql-servert-sqlconcatenationsql-null
TheStranger
TheStranger

Reputation: 1577

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

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 |
+-----+------+

Upvotes: 0

Views: 4983

Answers (3)

Ilyes
Ilyes

Reputation: 14928

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);

Upvotes: 1

Ankit Das
Ankit Das

Reputation: 650

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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;

Upvotes: 3

Related Questions