I am looking to concatenate four columns into a computed column. Any of the four columns could have null values. The resulting computed column should have no spaces - just the combination of the four fields.
I have tried about a dozen options including coalesce, IFNULL etc. Below is a sample of some of the code I have tried.
(CONCAT([Column1],[Column2],[Column3],[Column4]))
CONCAT((ISNULL, Column1, ''), (ISNULL, Column2, ''), (ISNULL, Column3, ''), (ISNULL, Column4, ''))
ISNULL(Column1, '') + ISNULL(Column2, '') + ISNULL(Column3, '')+ ISNULL(Column4, '')
CONCAT(((isnull([Column1],''), isnull([Column2],'')), isnull([Column3],'')), isnull([Column4],''))
CONCAT(COALESCE (Column1, N'') , COALESCE (Column2, N'') , COALESCE (Column3, N'') , COALESCE (Column4, N''))
Here is a sample of data and my desired outcome:
| Column1 | Column2 | Column2 | Column2 | RESULTS |
|---|---|---|---|---|
| XYZ | 5 | B | c | XYZ5Bc |
| UGH | 9 | a | UGH9a | |
| 8 | G | c | 8Gc | |
| IJH | K | b | IJHKb | |
| KUJ | 6 | Y | KUJ6Y |
Nowadays, many databases support the very convenient concat_ws() function. With that, you would simply use:
select concat_ws('', column1, column2, column3, column4)
If the values have spaces at the beginning or end, you can use trim():
select concat_ws('', trim(column1), trim(column2), trim(column3), trim(column4))
If your database does not support concat_ws() there are work-arounds. However, the specific work-around depends on the database you are using.