Reading a column value in unicode in sql server side

[tosachingupta]

Hello,
In our SQL Server 2008 database in extended property collation is set as SQL_Latin1_General_CP1_CI_AS'.
I have a table, which contains string ported from text file having ASCII, UTF-8 formatted strings. Our requirement is that, we need to read value as Unicode from this table and then process that and fill other tables. We are not sure whether data present in ASCII, UNICODE or UTF-8/16.

That' why we are looking for a procedure which can convert string to Unicode irrespective of encoding scheme.

Suppose in a table i have data is utf-8 format for e.g.

Carrer de Béjar

Calle de Antonio Machado

Calle del Canónigo Domenech

Carrer de Pau ArpÃ* i GalÃ*

Calle del Abad Oliba

I want them to read in Unicode format, I have tried using Collate but could not succeed.
Please share your solution.
Regards,
Sachin

[PeejAvery]

Working with databases, you have to watch the data twice...once when you insert into the database, and when reading from it. It's possible that the data inserted was not properly decoded and therefore won't display correctly no matter what you do to read it.

What are you using to iterate the database? Is this a custom app or are you just talking about SQL dumps?

[VictorN]

I have a table, which contains string ported from text file having ASCII, UTF-8 formatted strings. Our requirement is that, we need to read value as Unicode from this table ...

And what is the actual type(s) of data in this table?
char, nchar, varchar, binary, ...?