Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server nvarchar, UTF-8 and French accents

Posted on 2014-01-14
3
Medium Priority
?
2,960 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 1000 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 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

719 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