• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

Strange characters when I read data from SQL Server

Hi, I was wondering if anyone could help me. Sometimes, when I read data from my SQL Server 2005 database, I get back strange characters like this:

"Many babies look alike so you don’t want to rely on appearance alone."

I know it has something to do with the encoding. The database is using "SQL_Latin1_General_CP1_CI_AS"

Any advice would be appreciated.
0
jsimonuchc
Asked:
jsimonuchc
  • 5
  • 3
  • 3
2 Solutions
 
gplanaCommented:
Windows standard character encoding is Unicode UTF-16 LE, and your database is using Latin-1 (also known as ISO-8859-1).
See at your programming language for conversion functions, or  use the COLLATE statement on Transact-SQL: http://msdn.microsoft.com/es-es/library/ms184391.aspx
0
 
jsimonuchcAuthor Commented:
Would you be able to provide an example of how to use the collate function in a SELECT query? Most of the examples i can find demonstrate it in creating and altering tables.
0
 
ralmadaCommented:
Not sure you have a collation issue there. I would say the problem is that your column type is not NVARCHAR to display special unicode characters. Make sure you change it to NVARCHAR. And when you enter information with special characters you do it like
insert yourtable values (N'your special characters here')
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jsimonuchcAuthor Commented:
I can't control the data that goes into this database. I only have read access to the tables. I'm trying to fix the data as it comes back to me.
0
 
ralmadaCommented:
(please note the N' there before the text.)
If not you can also try converting to nvarchar, although I'm not sure it will fix the problem since the data is no longer unicode...
select cast(yourcolumn as nvarchar(max))
 
0
 
ralmadaCommented:
>> I can't control the data that goes into this database. I only have read access to the tables. I'm trying to fix the data as it comes back to me. <<
Can you at least verify if the column is NVARCHAR? If not, then, the answer is plain an simple. YOU CANNOT do anything about it.
0
 
jsimonuchcAuthor Commented:
The column is a "text" column.

So, the application that natively reads the data presents the data without any problems. It's only when I try to output it using my own ColdFusion apps that I run into problems. Do you know why this might be? What are they doing differently than I am?
0
 
gplanaCommented:
I don't agree. You can interpret the inserted characters with another encoding using COLLATE on your SELECT
 
SELECT field COLLATE collate_name
WHERE collate_name is the collation as for example Latin1_General_CS_AS
Please see this link> http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/
0
 
ralmadaCommented:
@gplana,
Collation refers to a set of rules that determine how data is sorted and compared, it has nothing to do with storing special characters.
 @jsimonichc
>> The column is a "text" column. <<
Unfortunately text is not NTEXT. therefore, no unicodes characters are supported.
 
0
 
ralmadaCommented:
Now if you're using Coldfusion, then you might want to try enabling utf enconding there, check the link below:
http://mysecretbase.com/ColdFusion_and_Unicode.cfm 
But at the SQL level there's nothing else you can do
0
 
gplanaCommented:
Collation is about sorting and ALSO about character encoding (Latin 1 in the example I have putted). But yo are right, if column is Text, I think there is no possibility to have Unicode, so in that case, conversion should be done on the programming language.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now