Gordowey wrote:
Hi all,
I have an easy question. I have a table with names, for example
name
--------
José
José
Albert
ALBERT
and I want to search in this table, using the "like", no matter has
accent, or case letters
For example:
like '%Jose%' I need to retrive both Jose and José
and the same for '%albert%' I need to retrieve ALBERT and Albert.
How can I do this?
Must I set an specific collation in table,..or something
Thanks
Alberto
and I use the "like" to search
Case insensitivity is easy. Just add a column or columns that are an
uppercase or lowercase version of the name column(s) and search on that.
This could be a generated column using upper, ucase, lower or lcase. It can
also be indexed and if you use a resonable statement will not result in a
table scan.
Accents are more difficult. At least in a UTF-8 database the accented
characters are multibyte. Translate will not translate between single and
multibyte characters. I believe that you may have to write a stored
procedure to monocase and strip the accented characters for insertion
update and search. I have not tried this but think that it might work with
Java or C.
Maybe somebody else knows a better way
Colin