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?
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?

0
pigmentartsAuthor Commented:
I'm using MSSQL 2005
0
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"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

http://www.phillhowson.com/projects/coldfusion/chr.cfm
0
pigmentartsAuthor Commented:
That did not resolve the issue, could I format the database output before comparison?
0
MuffyBunnyCommented:
yes, in your query, replace that character with 'Chr(226)' and see if that works
0
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.
0
Anthony PerkinsCommented:
And I said C#, I meant ColdFusion.
0
MuffyBunnyCommented:
The code I've provided has been ColdFusion code
0
Anthony PerkinsCommented:
My mistake.  Sorry.
0
_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


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

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.
0
_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 :)
0
Anthony PerkinsCommented:
I agree.  Please post your actual working solution.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.