2
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

t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
Mike
  • 751
  • 2
  • 10
  • 25
  • 1
    Try looking into this post http://stackoverflow.com/questions/3861782/sql-server-error-implicit-conversion-of-because-the-collation-of-the-value-is-u – Y.S Jan 21 '15 at 07:16
  • [This link may help you as you are using SQL 2008][1] [1]: http://stackoverflow.com/questions/8547737/sql-server-2008-express-concat-doesnt-exist – Sravani Annepu Jan 21 '15 at 07:34

4 Answers4

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
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
0

Try this

select CAST(FirstName AS VARCHAR) + ' ' + CAST(LastName AS VARCHAR)from Members 
or
select CONVERT(VARCHAR(100),FirstName) + ' ' + CONVERT(VARCHAR(100),LastName )from Members
Krish KvR
  • 1,034
  • 4
  • 11
  • 18
0

Look what collation is on your tempdb. You can see it in the properties.

This collation you have to add to your Statements to change the collation of the field temporary.

example:

Your database has a collation "Latin1_General_CS_AS", tempdb's collation is "Latin1_General_CI_AS" your statement has to look like this, because the dynamic SQL will process in tempdb

Ranjit
  • 45
  • 6
-1

try this

Select concat(concat(FirstName , ' '), LastName) as final from Members
koushik veldanda
  • 1,079
  • 10
  • 23