SQL Server nvarchar, UTF-8 and French accents

Posted on 2014-01-14
Last Modified: 2014-03-12

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


Question by:1Cougar

Author Comment

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)
/* 2050 */
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

Open in new window

LVL 39

Assisted Solution

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.
LVL 38

Accepted Solution

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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

786 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