Solved

Strange characters when I read data from SQL Server

Posted on 2010-09-07
11
656 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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 …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

832 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