Reading Special Characters from Database

Hi All,

I seem to have problem with a system I'm working with at the moment. I have a special character issue, I have a database table with some Welsh content and need to compare the content in a case statement in order to display a result. At the moment the I can't seem to match the anything as there are problems with the character encoding I think.

Here is an example of the scenario I face:

statement: "Gofyn i dy ffrindiau beidio â bod mor swnllyd gan fod beth 'rydych chi'n ei wneud yn amlwg yn achosi gofid i bobl eraill"

This needs to be matched using a case statement to display appropriate response but  the special character 'â' is being a thorn in my flesh. On output it appears the same.

Help..


switch(db.res_answer)
     {//Question 1
	 
	 case "Gofyn i dy ffrindiau beidio â bod mor swnllyd gan fod beth 'rydych chi'n ei wneud yn amlwg yn achosi gofid i bobl eraill":
	{ msg="blaa blaa blaa";
        break;
     }

Open in new window

LVL 12
pigmentartsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MuffyBunnyCommented:
What database are you using?

pigmentartsAuthor Commented:
I'm using MSSQL 2005
MuffyBunnyCommented:
You'll have to break up your string in your WHEN clause and use ASCII codes for your special characters

case "Gofyn i dy ffrindiau beidio â bod mor swnllyd gan fod beth 'rydych chi'n ei wneud yn amlwg yn achosi gofid i bobl eraill"

change to:
case "Gofyn i dy ffrindiau beidio " + Chr(226) + " bod mor swnllyd gan fod beth 'rydych chi'n ei wneud yn amlwg yn achosi gofid i bobl eraill"
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MuffyBunnyCommented:
Here's a great resource for the code values

http://www.phillhowson.com/projects/coldfusion/chr.cfm
pigmentartsAuthor Commented:
That did not resolve the issue, could I format the database output before comparison?
MuffyBunnyCommented:
yes, in your query, replace that character with 'Chr(226)' and see if that works
Anthony PerkinsCommented:
I am confused you have posted C# code and yet you want T-SQL code?  At least that is what has been offered so far.
Anthony PerkinsCommented:
And I said C#, I meant ColdFusion.
MuffyBunnyCommented:
The code I've provided has been ColdFusion code
Anthony PerkinsCommented:
My mistake.  Sorry.
_agx_Commented:
there are problems with the character encoding I think

Did you verify the text is being being stored in the db correctly? The comparison worked fine for me using ms sql 2005 / default driver / and an nvarchar column.  (Though you might consider assigning a number or a code to the phrase instead. Then compare that instead of long strings of text ..)

<cfquery name="q" datasource="#dsn#">
create table test (content nvarchar(255))
</cfquery>

<cfquery name="q" datasource="#dsn#">
insert into test (content)
values ( <cfqueryparam value="Gofyn i dy ffrindiau beidio â bod mor swnllyd gan fod beth 'rydych chi'n ei wneud yn amlwg yn achosi gofid i bobl eraill" cfsqltype="cf_sql_varchar">)
</cfquery>

Open in new window


psvineeshCommented:
Encode all the fields before comparing
Eg: x=urlencode(stringdata)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pigmentartsAuthor Commented:
This should work but used another solution.
_agx_Commented:
This should work

Does it actually work? (I didn't test it).  Also it'd help to future searcher's to know what you DID you end up using :)
Anthony PerkinsCommented:
I agree.  Please post your actual working solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.