How to search with Accent Insensitive and Case Insensitive

Gordowey

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

Nov 30 '07 #1

Colin Booth

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

Dec 1 '07 #2

Gordowey

On 1 dic, 22:17, Colin Booth <colinsbo...@gmail.comwrote:

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- Ocultar texto de la cita -

- Mostrar texto de la cita -

Thanks for your help. I appreciate !

I was trying to avoid to have to duplicate the information in the
tables, I mean

original aux
=================
José Martín jose martin

is there another aproach?
thanks, Alberto

Dec 3 '07 #3

Colin Booth

Gordowey wrote:

On 1 dic, 22:17, Colin Booth <colinsbo...@gmail.comwrote:

>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- Ocultar texto de la cita -

- Mostrar texto de la cita -

Thanks for your help. I appreciate !

I was trying to avoid to have to duplicate the information in the
tables, I mean

original aux
=================
José MartÃ*n jose martin

is there another aproach?
thanks, Alberto

If you have many rows and the search is frequently used, duplication is
probably the fastest option. Functions such as translate will table scan
and I think will apply the translation to every row. This can be slow and
eat machines. Also %like% will not be good on large tables

Colin

Dec 4 '07 #4