(Select FirstName + ' ' + LastName from Members gives error Implicit conversion of varchar value to varchar cannot be performed

Question

Select FirstName + ' ' + LastName from Members 

Gives error:

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

But it works fine when I take FirstName and LastName in separate columns like

Select FirstName, LastName From Members

I want to pick First and Last name in one column

Answer 1

Seems you have different collation within the same table - very unusual

Try this:

SELECT 
  FirstName COLLATE DATABASE_DEFAULT + ' ' 
    + LastName COLLATE DATABASE_DEFAULT AS FullName
FROM Members 

If this works, I suggest you change collation to be the same for FirstName and LastName rather than correcting your query.

This is an example of how to change collation:

ALTER TABLE Members 
  ALTER COLUMN LastName 
    varchar(20) COLLATE DATABASE_DEFAULT NOT NULL