Search Tools Links Login

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!

Original Author: Carl Mercier

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! :)

Assumptions

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!

Code

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

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 108 times

Categories

Visual Basic 6

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.