Solved

Strange characters when I read data from SQL Server

Posted on 2010-09-07
11
658 Views
Last Modified: 2012-05-10
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
Comment
Question by:jsimonuchc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 33617872
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
 
LVL 1

Author Comment

by:jsimonuchc
ID: 33618152
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
 
LVL 41

Expert Comment

by:ralmada
ID: 33620872
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 1

Author Comment

by:jsimonuchc
ID: 33620887
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
 
LVL 41

Expert Comment

by:ralmada
ID: 33620983
(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
 
LVL 41

Expert Comment

by:ralmada
ID: 33621071
>> 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
 
LVL 1

Author Comment

by:jsimonuchc
ID: 33621113
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
 
LVL 15

Expert Comment

by:gplana
ID: 33621144
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
 
LVL 41

Expert Comment

by:ralmada
ID: 33621201
@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
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 250 total points
ID: 33621230
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
 
LVL 15

Accepted Solution

by:
gplana earned 250 total points
ID: 33621290
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question