Concatenate two columns which have a space between them

Question

Concatenate two columns which have a space between them

Red27

SSC Veteran

Points: 234
More actions
October 16, 2017 at 7:04 am

#340759

Hi guys,
I would like to concatenate two columns which have a space between them. I keep getting an error message. the code is below. any help would be appreciated.
CONCAT(First Name, ' ', Last Name)

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99138 More actions · Answer 1

Without the error message, this is a guess, but do you column names have spaces in them? To refer to a field with whitespace in, you need to encapsulate the name in square brackets ([ ]). For example:
CONCAT([First Name], ' ', [Last Name])
Personally, where possible, I would avoid using column names with whitespace in. Use CamelCase, or Underscores instead.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99138 More actions · Answer 2

Without the error message, this is a guess, but do you column names have spaces in them? To refer to a field with whitespace in, you need to encapsulate the name in square brackets ([ ]). For example:
CONCAT([First Name], ' ', [Last Name])
Personally, where possible, I would avoid using column names with whitespace in. Use CamelCase, or Underscores instead.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Red27 SSC Veteran Points: 234 More actions · Answer 3

I am still getting an error message:
Msg 189, Level 15, State 1, Line 45
The concat function requires 2 to 254 arguments.

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 4

adan950 - Monday, October 16, 2017 8:16 AM
I am still getting an error message:
Msg 189, Level 15, State 1, Line 45
The concat function requires 2 to 254 arguments.

You have posted in the 2008 forum and I don't think CONCAT() works in 2008.

Thanks

BrainDonor SSCoach Points: 19262 More actions · Answer 5

adan950 - Monday, October 16, 2017 8:16 AM
I am still getting an error message:
Msg 189, Level 15, State 1, Line 45
The concat function requires 2 to 254 arguments.

CREATE TABLE names ( [First Name] VARCHAR(20) , [Last Name] VARCHAR(20) );

INSERT INTO dbo.names ( [First Name] , [Last Name] ) VALUES ( 'John', 'Smith' );

SELECT CONCAT([First Name], ' ', [Last Name]) FROM names;

This code works fine. Did you copy/paste your code segment in your initial message, or type it yourself? You might not have reproduced the exact code you're having a problem with.
Without the actual code you're using we're just guessing.

Steve Hall
Linkedin
Blog Site

Thom A SSC Guru Points: 99138 More actions · Answer 6

adan950 - Monday, October 16, 2017 8:16 AM
I am still getting an error message:
Msg 189, Level 15, State 1, Line 45
The concat function requires 2 to 254 arguments.

What is the exact SQL you're running? For example, this works fine:
CREATE TABLE #test ([First name] varchar(10), [Last name] varchar(10)); INSERT INTO #test VALUES ('Joe','Bloggs'); SELECT CONCAT([First name],' ',[Last Name]) AS FullName FROM #test; DROP TABLE #test;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004596 More actions · Answer 7

NorthernSoul - Monday, October 16, 2017 8:34 AM
adan950 - Monday, October 16, 2017 8:16 AM
I am still getting an error message:
Msg 189, Level 15, State 1, Line 45
The concat function requires 2 to 254 arguments.
You have posted in the 2008 forum and I don't think CONCAT() works in 2008.
Thanks

I think that's likely the answer to the problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MarkW.Rhythm1 SSC Veteran Points: 273 More actions · Answer 8

Not sure if this is the answer but I am using the following in 2008 and it works great

TableName.[First Name] + ' ' + TableName.Last Name

Concatenate two columns which have a space between them

Cookies on SQLServerCentral