Solved

SQL Server nvarchar, UTF-8 and French accents

Posted on 2014-01-14
3
2,509 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
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 38

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now