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 |
+-----+------+
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 :
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP