Removing the accents from comparaison

[Warrio]

Hello!
In order to avoid double entry data, like
'Hällo' that should be concidered the same as 'Hallo' or 'Français' that is
the same as 'Francais', is possible to do a comparaison of the fields
contained on a table with a field typed by the user withouth taking in
concideration all the accents and punctuation?

because if I run the query below that should detect if the field entred by
user exist already or not

StrSql = "SELECT myField FROM myTable WHERE myField LIKE """ & TextBox &
""""
in this case, if the TextBox contains 'Noël' and 'Noel' already exist, I
would never be able to see it with this query.

Thanks for any advice!

 

[Michel Walsh]

Hi,


Jet is case insensitive but accent sensitive. MS SQL Server can have
different setting, such as case sensitive and accent insensitive, if you
want, or other mixture, and MS SQL Server 2005 should be able to change that
kind of setting, "ON THE FLY", but MS SQL Server 2000 requires a
re-installation (or second copy) of the engine (if memory serves, and if no
service patch now allows that change "on the fly"). That change is likely to
require the index on string values to be rebuild. Not something you
necessary want.

So, basically, you may be obliged to use Replace (ACCESS 2000 or later),
to replace the accents in the input, and, for values in the table, add a
column, or a table, of aliases.


Aliases ' table name
This aka ' fields name
Noël Noel
Québec Quebec
Québec Quebec City
Ottawa Bytown
Istanbul Constantinople
Istanbul Byzance
....

and so on. You could then start to see if the name you look for is in the
aka and if so, use the "this" value than the original value, to continue you
research. The advantage of the table (by comparison to the extra field) is
that it allows multiple aliases for a given name.



Hoping it may help,
Vanderghast, Access MVP

 

[Warrio]

Thank you for the quick answer!
How ever it's weird that there is no function to bypass the accent sensitive
problem.. cause with you the case sentivity, we can use ucase or lcase.. and
other string function..
any way I think I'll use the function Replace by replacing the most common
accents with the normal characters, because I'm not comparing only one field
and only one table so I cannot manage many fields plus an alias for each of
them

Thanks a lot again!