Solved

Strange characters when I read data from SQL Server

Posted on 2010-09-07
11
659 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

696 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