We help IT Professionals succeed at work.

Concatenate two text fileds in a view useing SQL Management Studio

ISBTECH used Ask the Experts™
I'm building a view in MS SQL 2005 that will be used to import data to an access database.  I can pull everthing I need but the sales persons name in access is one field not two so I need to concatenate the fields SPFIrstName and SPLastname.  I know it should be something like dbo.users.SPFirstName + " " + dbo.users.SFLastName as SPName but that isn't working in the select statement.  What am I doing wrong?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


Here is the select statement as I have it now.

 dbo.loan_main.actualclosingdate, dbo.loan_main.applicationdate, dbo.customer_main.firstname, dbo.customer_main.lastname,
                      dbo.customer_group.primarycustomer, dbo.CB_Investor_View.alias2, dbo.Users.SPFirstName + " " + dbo.Users.SPLastName AS LOName,
                       dbo.loan_main.loanid, dbo.loan_main.loanamount
AneeshDatabase Consultant
Top Expert 2009

isnull(dbo.Users.SPFirstName+' ', '') + isnull(dbo.Users.SPLastName,'')  AS LOName
Bhavesh ShahLead Analyst
Top Expert 2010


isnull(dbo.Users.SPFirstName+'') + ' ' + isnull(dbo.Users.SPLastName,'')  AS LOName

Open in new window


I see that I'm useing " where I should be using ' but what doest the isnull do?
Database Consultant
Top Expert 2009
thats one change,
the isnull () function will return a '' in case the firstname is null. in sql server, by default the operations involving NULL will yield in a NULL output
You should use the one I provided, your query can retrun a name starting with a  ' ' in case the first name is blank


Thanks I see where including the ' ' in the isnull statement I only get the ' ' if there is a first name.

Great answers thanks!!