Solved

SQL Server nvarchar, UTF-8 and French accents

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

627 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