Querying special characters

Question

I work with language with special characters (example: e -> é). I have a query with a LIKE but when I search a word with special char and my query doesn't have the special char, I don't get the results I expect:

select * from a where name like '%e%'

The above returns no results when the database contains "dés". Is there a way to have this query return the desired results.

Answer 1

You may need to try something adventurous like coding in the escape sequences.

select * from a where name like '%' & chr$(numberforcharacter) & %'

Answer 2

If the collation of your database is set to accent insensitive, then this should work (for most accented characters).

For example in the collation Latin1_General_CI_AS, the CS means Case Insensitive, Accent Sensitive. You would want the collation to be Latin1_General_CI_AI which means Case Insensitive, Accent Insensitive

Be warned though, that not all "accented" characters are created equally, but for spanish and french searches I found things work out as expected.

Answer 3

I finally replace special char that I know by a %