asked on

SQL Server nvarchar, UTF-8 and French accents

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,

1Cougar

ASKER

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

SOLUTION

thenelson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Aaron Tomosky

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial