?
Solved

Strange characters when I read data from SQL Server

Posted on 2010-09-07
11
Medium Priority
?
660 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 750 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 750 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 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