WHERE's against words with accents

[H. Martins]

Hi.

In my language people can have (say) the same name with or without
accents. As example, We can have Luis or Luís.

This is a simple case where the name has just one accent, but if we do
a query with two names having accents we may find ourselves with four
different accent possibilities.

I thought I could make a shadow field with accents removed (another
problem) and issue the WHERE against this field.

Is there any other approach?

Thanks~Henry

 

[Douglas J. Steele]

Not quite sure what you mean by "make a shadow field with accents removed".
If you're talking about storing that in the table, you'd be better off
creating a function that strips the accents from the field, and then using
that function in your query. In that way, you don't have to worry that
someone might update the "real" field and forget to update the shadow field.

Make sure you apply the function both to the name field in the table AND to
the parameter being supplied by the user, just in case they included
accents.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi.

In my language people can have (say) the same name with or without
accents. As example, We can have Luis or Luís.

This is a simple case where the name has just one accent, but if we do
a query with two names having accents we may find ourselves with four
different accent possibilities.

I thought I could make a shadow field with accents removed (another
problem) and issue the WHERE against this field.

Is there any other approach?

Thanks~Henry

 

[Jeff Boyce]

Henry

You've described "how" you're trying to do something, but not "what" ... or
at least, I'm left guessing that you want to give users of your application
a way to find all possible combinations, with and without accents (in case a
name was entered incorrectly).

Or have I missed your underlying business need?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi.

In my language people can have (say) the same name with or without
accents. As example, We can have Luis or Luís.

This is a simple case where the name has just one accent, but if we do
a query with two names having accents we may find ourselves with four
different accent possibilities.

I thought I could make a shadow field with accents removed (another
problem) and issue the WHERE against this field.

Is there any other approach?

Thanks~Henry

 

[Henry]

Jeff,

I need to query the database to find people. In my language people
names may have accents or not. So if we have to look after (some) Luís
we have to try Luís and Luis. If the guy is called Luis Vitor, we have
to try Luis Vitor, Luís Vitor, Luís Vítor and Luis Vítor.

Douglas solution seems OK but I don't know how to remove accents.Of
course I can always make a table of characters with and without
accents and browse the guy's name, but it looks like it will occupy
CPU cycles.

Henry

 

[KARL DEWEY]

I think this means an alias field.

Yes, using Henry' idea of a table of characters with and without accents
with the replace function.
No_Accent: Replace([nameField], [ConvTable].[AccentField],
[ConvTable].[NoAccent])

 

[David W. Fenton]

:

I need to query the database to find people. In my language people
names may have accents or not. So if we have to look after (some)
Lu¡s we have to try Lu¡s and Luis. If the guy is called Luis
Vitor, we have to try Luis Vitor, Lu¡s Vitor, Lu¡s V¡tor and Luis
V¡tor.

I think I would use LIKE and wildcards, so that I would search for:

LIKE "Lu?s V?tor"

That might get you some records that aren't what you are looking
for, but at least it would get you all the ones you actually want.

The other alternative is to write some code to substitute all the
possibilities:

Input: NameField = "Luis Vitor"

Output:
NameField = "Luis Vitor" OR NameField = "Luís Vítor"
OR NameField = "Luis Vítor" OR NameField = "Luís Vitor"

I'm not sure how to write the code to produce that, though, as it
would need to be pretty smart about all the combinations. For
instance, say there were two different accented characters -- in
that case, you'd have double the number of criteria (or maybe it's
4X).

That's why I think the wildcard solution is much better. You could
then filter out the incorrect matches from the resultset.