By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,611 Members | 1,693 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,611 IT Pros & Developers. It's quick & easy.

How to search with Accent Insensitive and Case Insensitive

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.