Solved

SQL Server nvarchar, UTF-8 and French accents

Posted on 2014-01-14
3
2,757 Views
Last Modified: 2014-03-12
Hello,

I have a MS SQL Server 2005 database.  The nvarchar fields are: SQL_Latin1_General_CP1_CI.  I have a front end on the web (php) using these tables.  I have English and French stored in database.

For some time now, I have been seeing occasional issues such as a 'black diamond with a question mark' appearing where an accented character should be or strange characters such as: 'Fréderic' instead of 'Fréderic'.  My web pages were encoded to ISO-8859-1.  I changed them to UTF-8 as well as the php files querying the SQL Server database and updating the database.  Everything seemed to be fixed as on the web it looked fine.  However, today, when I wanted to run a report in SQL Server I found that every é I had updated to the database from the web (UTF-8) was stored as 'é'.

Is there a way to cast the columns in SQL Server so the data displays properly when running a query in SQL Server (or a report in Access as the SQL Server DB is also connected to an Access project I have)?  If I store it with correctly appearing accents in SQL Server then the data doesn't appear correctly on the UTF-8 charset web pages.

I have spent several hours on this and don't know what to do....

Thanks!

Cheers,
0
Comment
Question by:1Cougar
[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
3 Comments
 

Author Comment

by:1Cougar
ID: 39779291
This is what I have done to fix the problem but don't know if it is the best.  I added the function below.  Don't know if there might be a better solution....

Any comments would be appreciated!


ALTER FUNCTION [dbo].[udfFormatAccent] (@ValIn nvarchar(100))
RETURNS nvarchar(100)
 AS  
/* 2050 */
BEGIN
DECLARE @ValOut nvarchar(100)
DECLARE @ValCurr nvarchar(100)
set @ValCurr=REPLACE(@ValIn,'é','é')
set @ValCurr =REPLACE(@ValCurr, 'Ã', 'à')
set @ValCurr = REPLACE(@ValCurr, 'ô', 'ô')
set @ValCurr = REPLACE(@ValCurr, 'â', 'â')
set @ValCurr = REPLACE(@ValCurr, 'î', 'î')
set @ValCurr = REPLACE(@ValCurr, 'è', 'è')
set @ValCurr = REPLACE(@ValCurr, 'ê', 'ê')

set @ValOut = @ValCurr
RETURN @ValOut
END

Open in new window

0
 
LVL 39

Assisted Solution

by:thenelson
thenelson earned 250 total points
ID: 39781282
I have run into a similar problem when communicating between MS Word and MS Access. For example Word uses left and right quotation marks and Access uses just straight quotation marks. I ended up using the replace function also.

You might be able to use the Choose function or Switch Function instead of all those Replace functions. I think either one would run faster than a series of Replace functions if that is a concern.


Choose(index, choice-1[, choice-2, ... [, choice-n]])

The Choose function syntax has these parts:

Part Description
index Required. Numeric expression or field that results in a value between 1 and the number of available choices.
choice Required. Variant expression containing one of the possible choices.



Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

The Switch function syntax has these parts:

Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the corresponding expression is True.
0
 
LVL 39

Accepted Solution

by:
Aaron Tomosky earned 250 total points
ID: 39781311
How is php getting the data into mssql? You can do it lots of ways and the website will be pale to pull it out correct even though ssms and access will see some junk.
However if you put it into mssql correctly, then this problem will go away. I think this is the link that I followed when I needed to do this, but I also may have used the ado.db library
http://technet.microsoft.com/en-us/library/cc626307(v=sql.105).aspx
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

752 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