Skip to main content
J
janLynn_tn
Participant
May 13, 2012
Question

Query with special characters

  • May 13, 2012
  • 1 reply
  • 725 views

I am having a problem returning results that include special characters. I have a list of individuals and want to allow an alpha select to display, for example, only the individuals who's last name starts with C.

I have an individual in this list who's last name is Čapek

When I do a mySQL query in mySQL Query Browser as follows, this individual is returned in results.

SELECT distinct

       last_name

FROM

       individuals

WHERE

       last_name like 'C%'

HOWEVER, when I use the same query in a cfquery, this individual's record is not returned.

I am using mySQL 5 with ColdFusion 9 and have the mySQL connection string settings: useUnicode=true&characterEncoding=UTF-8

Any assistance would be appreciated - I've not found anything useful in my Google searches.

Many Thanks!

    This topic has been closed for replies.

    1 reply

    A
    Adam Cameron.
    Inspiring
    May 14, 2012

    I've not personally come across this issue before, but googling for "mysql where diacritic" (and various permutations thereof) came up with this doc, which seems to discuss your issue: http://bugs.mysql.com/bug.php?id=37413

    So it could be that the JDBC driver is using a different collation than your other MySQL client (wild guess)?

    Try messing around with the collation being used, and see if that helps at all?  The JDBC param to set seems to to connectionCollation be (from http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html).

    Sorry if this sounds vague or is completely wrong - it's a bit of a guess - but it does sound reasonably likely to be something to do with your issue, given your description.

    --

    Adam

    J
    janLynn_tnAuthor
    Participant
    May 14, 2012

    Thanks Adam! I'll try messing around with the collection and let you know if I'm successful.

    Also, thanks for the tip on what to call the 'special character' in my future Google searches!!

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices