Mark,

Thank you. This worked. Thanks for the link. I will surely read it.

Hi Steve,

I was in such a hurry that i clicked post instead of preview. I realized later. I am sorry for that.

Thanks you both. Adi's suggestion worked for me.

Thanks, you were right. Different column had different collation.

I used alter statement for each of the column and it worked.

"ALTER TABLE Table_Name ALTER COLUMN Column_Name COLLATE SQL_Latin1_General_CP1_CI_AS"

Perfect!! It worked. Thanks o much, GSquared.

I used your script for the table below:

create table test

(col1 varchar(30),

col2 varchar(30),

col3 varchar(30))

insert into test (col1, col2, col3)

values('hi there', '111', '')

insert into test (col1, col2, col3)

values('', '', 'Hello')

insert...

sorry,this was suppose be my new topic. Ignor this added a new topic: How to remove space.

I am working on SQL 2005

I need to store values from different fields in a new field. No need to take the column if no value. Something like this:

col1 ...

Thank you for your suggestions.