CODEWITHSUNDEEP
sql-servert-sql
MR.ABC
MR.ABC

Reputation: 4862

Concatenate columns together, columns can be null

I need to bring values together and copy it to a diffrent column.

COLUMN 1 | COLUMN 2 |  COLUMN 3 | COLUMN 4 
Hallo      out         there      Hallo out there
My         NULL        name is    My name is
I'm        a           rabbit     I'm a rabbit

How to merge column 1, 2, 3 and copy it to column4 separated with space.

Columns can be null.

Upvotes: 0

Views: 1314

Answers (6)

AnandPhadke
AnandPhadke

Reputation: 13506

update yourtable SET COlumn4= ltrim(rtrim(isnull([COLUMN 1],'') +' '+isnull([COLUMN 2],'') +' '+  isnull([COLUMN 3],'')))

Upvotes: 0

Turque
Turque

Reputation: 717

select isnull(convert(varchar(255),[COLUMN1]),'')+' '+isnull(convert(varchar(255),[COLUMN2]),'')+' '+isnull(convert(varchar(255),[COLUMN3]),'')
from table

UPDATE table
SET COLUMN4 = isnull(convert(varchar(255),[COLUMN1]),'')+' '+isnull(convert(varchar(255),[COLUMN2]),'')+' '+isnull(convert(varchar(255),[COLUMN3]),'')

Above codes may not work depending on datatypes so you may convert it to varchar before.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453940

SQL Server 2012

UPDATE table
SET Column4 = CONCAT(Column1 + ' ', Column2 + ' ', Column3)

Upvotes: 3

podiluska
podiluska

Reputation: 51514

Using ISNULL

UPDATE table
SET Column4 = 
ISNULL(Column1+' ','') + 
ISNULL(Column2+' ','') + 
ISNULL(Column3,'')

Or you could consider using a calculated column.

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

UPDATE dbo.table
  SET column4 = COALESCE(column1, '') 
      + COALESCE(' ' + column2, '')
      + COALESCE(' ' + column3, '');

Upvotes: 4

Joe G Joseph
Joe G Joseph

Reputation: 24116

Just use + sign

  select ISNULL([COLUMN 1],'')+' ' +
         isnull([COLUMN 2],'')+' ' +
         isnull([COLUMN 3],'') 
  from your_table

Upvotes: 2

Related Questions