Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Access Accent Sensitivity

    Hi

    How do I make JET\ ACCESS SQL accent sensitive? The below inserts ô and o into a table, and should return only letters between a and z - it returns both. How can I make Access respect Accents?

    Code:
    create table testTable(testCol TEXT(1))
    
    INSERT INTO testTable(testCol)
    SELECT Chr(244)
    
    INSERT INTO testTable(testCol)
    SELECT Chr(111)
    
    SELECT *
    FROM testTable
    WHERE testCol LIKE "[a-z]"
    Although I've only used a single character entry for the example, the real data is composed of text strings: I'm trying to find non-alphanumerics using a construct similar to: LIKE "*[!a-zA-Z0-9]*"

    Chars!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - I think ignore - I've checked the interwebs for case sensitivity searches and even that dog won't hunt. I was only using Access for some QAD data cleaning. Turns out it was even dirtier than I expected
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Glad I could help
    George
    Home | Blog

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mmmmmmmurky topic poots.
    sitting here in 4(official)+1(english)+N(other) language Switzerland i am "àcçëñt-énäblêd" (yup - don't bother telling me some of those dont make sense)

    here's an immediate-window paste:
    ? asc("é")
    233
    ? asc("è")
    232
    ? asc("e")
    101


    here is a recordset
    SELECT * FROM tblWot
    idWot strWot
    1 woté
    2 wotè
    3 wote

    here are three SQL strings that perform EXACTLY as they should (returning 1 (correct) row each from tblWot):

    SELECT tblWot.idWot, tblWot.strWot
    FROM tblWot
    WHERE (((tblWot.strWot) Like "*e*"));

    SELECT tblWot.idWot, tblWot.strWot
    FROM tblWot
    WHERE (((tblWot.strWot) Like "*è*"));

    SELECT tblWot.idWot, tblWot.strWot
    FROM tblWot
    WHERE (((tblWot.strWot) Like "*é*"));

    ...so A handles this stuff pretty well.
    what exactly are you trying to do ?

    izy


    LATER

    to avoid possible accusations of being obscure: sure it works when i add "wôte" to the table and query
    SELECT tblWot.idWot, tblWot.strWot
    FROM tblWot
    WHERE (((tblWot.strWot) Like "*ô*"));


    (((((((((the excess parentheses all come from A)))))))))
    Last edited by izyrider; 01-05-09 at 14:50.
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks Izzy

    You are right - everything you have shown works for me. I'll just set up my tables & data:
    Code:
    CREATE TABLE tblWot (idWot INTEGER, strWot TEXT(10))
    
    INSERT INTO tblWot(idWot, strWot)
    SELECT 1, "woté"
    
    INSERT INTO tblWot(idWot, strWot)
    SELECT 2, "wotè"
    
    INSERT INTO tblWot(idWot, strWot)
    SELECT 3, "wote"
    
    SELECT * FROM tblWot
    idWot strWot
    1 woté
    2 wotè
    3 wote
    These also work fine:
    Code:
    SELECT tblWot.idWot, tblWot.strWot
    FROM tblWot
    WHERE (((tblWot.strWot) Like "*e*"))
    
    ....Like "*è*"
    
    
    ....Like "*é*"
    So a search for literals is no problem. However, this should return two rows:
    Code:
    SELECT tblWot.idWot, tblWot.strWot
    FROM tblWot
    WHERE tblWot.strWot Like "*[!a-z]*"
    The above means "return all rows where strWot contains one or more characters that are not between a and z". It returns zero rows, so is evaluating e, è and é as equivalent (or at least all between a and z), even though there ASCII values are wildly different. To prove the concept works in itself:
    Code:
    SELECT tblWot.idWot, tblWot.strWot
    FROM tblWot
    WHERE tblWot.strWot Like "*[!a-v]*"
    This returns all rows, as W is not between a and v.

    If I use an accent insensitive collation in SQL Server, I get the same effect for the BETWEEN evaluation, but if I use an accent sensitive collation then two rows are returned by my query. I can provide more details if that doesn't mean much to you.

    Finally, the English description of what I want to do is identify all rows that contain "unusual" characters. All my text is British English. There should be next to no accents on characters but there is some contamination in the data. I want to find these rows.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Further:
    Code:
    SELECT tblWot.idWot, tblWot.strWot
    FROM tblWot
    WHERE tblWot.strWot Like "*[d-e]*"
    Results in one row (wode).
    Code:
    SELECT tblWot.idWot, tblWot.strWot
    FROM tblWot
    WHERE tblWot.strWot Like "*[d-f]*"
    Results in all three rows. So the accented e's are greater than e but less than f.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Whoops! T-SQL below.
    Code:
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.tblWot_AS')) BEGIN
        DROP TABLE dbo.tblWot_AS
    END
    
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.tblWot_AI')) BEGIN
        DROP TABLE dbo.tblWot_AI
    END
    
    CREATE TABLE dbo.tblWot_AS 
        (
              idWot        INT
            , strWot    VARCHAR(10) COLLATE Latin1_General_CI_AS
        )
    
    
    CREATE TABLE dbo.tblWot_AI
        (
              idWot        INT
            , strWot    VARCHAR(10) COLLATE Latin1_General_CI_AI
        )
    
    INSERT INTO dbo.tblWot_AS(idWot, strWot)
    SELECT 1, 'woté'
    UNION ALL
    SELECT 2, 'wotè'
    UNION ALL
    SELECT 3, 'wote'
    
    INSERT INTO dbo.tblWot_AI
    SELECT    *
    FROM    dbo.tblWot_AS
    
    SELECT    idWot, strWot
    FROM    dbo.tblWot_AI
    WHERE    strWot Like '%[^a-z]%'
    
    SELECT    idWot, strWot
    FROM    dbo.tblWot_AS
    WHERE    strWot Like '%[^a-z]%'
    My error - I though accented characters don't fall within the range in both cases. Turns out they do.

    Ho hum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    For completeness I thought I'd post what I should have been doing all along in SQL Server.
    Code:
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.tblWot_BIN')) BEGIN
        DROP TABLE dbo.tblWot_BIN
    END
    
    CREATE TABLE dbo.tblWot_BIN 
        (
              idWot        INT
            , strWot    VARCHAR(10) COLLATE Latin1_General_BIN
        )
    
    INSERT INTO dbo.tblWot_BIN(idWot, strWot)
    SELECT 1, 'woté'
    UNION ALL
    SELECT 2, 'wotè'
    UNION ALL
    SELECT 3, 'wote'
    
    SELECT    idWot, strWot
    FROM    dbo.tblWot_BIN
    WHERE    strWot Like '%[^a-z]%'
    Basically I should have been looking to do a binary comparison, not an accent sensitive one.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are 100% right!

    A
    STON
    ISH
    ING
    behaviour from A!

    i don't have time to play with this right now, but will look at it again.

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Interesting topic
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    after reading thru a thousand web pages i give up trying to do this "properly" in JET-SQL.

    i looked at a few hundred french pages ...there is discussion on the inverse problem: a SELECT that will pull both 'François' and 'Francois'. many workarounds to this inverse problem add a second field holding the name stripped of accents (the evil "calculated value") and query the stripped field using user-input also stripped of accents.

    i didn't find any discussion of [!a-z] on french forums!
    it might be there: search engines (Google, various forums) are unhelpful - they fold "[!a-z]" in exact-phrase queries into "az".

    Michael Kaplan's blogs (& trigeminal.com) rattle the cage of the issue without offering hope.

    the only A-way i could get working used a VBA function called by the query.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •