• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Accent Insensitive database querying

Accent Insensitive database querying

Since MS-Access doesn't support accent insensitive queries by itself (MS SQL Server does as far as I know), I had to create a function that would fix the problem. With this function, it is possible to turn any SQL query into an accent insensitive query. With a few little modifications, it works great with ASP too!

Inputs
Example: STRSQL = "SELECT * FROM MyTable WHERE animal LIKE '%" & AccIns("ELEPHANT") & "%'" This will return any record where animal = Élephant, Elephant, éléphant, eléphant, etc. You get the picture. Now have fun! :)

Assumes
You need to know how SQL queries work.

Returns
An accent insensitive string to use against a database.

Side Effects
My friend's computer exploded last time he used this function, so watch out!

Rate Accent Insensitive database querying

• 1
• 2
• 3
• 4
• 5

(9(9 Vote))

Function AccIns(Str As String) As String
  Dim CurLtr As String * 1
  
  For x = 1 To Len(Str)
    CurLtr = Mid(Str, x, 1)
    
    Select Case CurLtr
      Case "e", "é", "è", "ê", "ë", "E", "É", "È", "Ê", "Ë"
        AccIns = AccIns & "[eéèêëEÉÈÊË]"
      Case "a", "à", "â", "ä", "A", "À", "Â", "Ä"
        AccIns = AccIns & "[aàâäAÀÂÄ]"
    
      Case "i", "ì", "ï", "î", "I", "Ì", "Ï", "Î"
        AccIns = AccIns & "[iïîìIÏÎÌ]"
    
      Case "o", "ô", "ö", "ò", "O", "Ô", "Ö", "Ò"
        AccIns = AccIns & "[oôöòOÔÖÒ]"
      Case "u", "ù", "û", "ü", "U", "Ù", "Û", "Ü"
        AccIns = AccIns & "[uûüùUÛÜÙ]"
    
      Case "c", "ç", "C", "Ç"
        AccIns = AccIns & "[cCçÇ]"
      
      Case Else
        AccIns = AccIns & CurLtr
    End Select
  Next
End Function