What I need to do is to convert a field to its equivalent in ASCII mode,
so that I can perform searches with accented characters (ISO8850-1).

Please consider table with three fields:
id, text_accented, text_ascii

Now, what I do is:
0. Only id and text_accented are filled up.
1. dump table with field of accented characters;
2. iconv the dump file to ASCII
3. import the dump to another schema
4. update initial table, the ASCII field, with text from the new table.

This process is time consuming and very manual.
Is there a way I can do it with a single update command or with a trigger?
I don't find a suitable function for this.


Luis

El 21/03/2011 23:40, Luis P. Mendes escribió/wrote:
The usual approach is to set a proper collation and let MySQL do the
search...

CREATE TABLE `test` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`text_accented` VARCHAR(50) NULL COLLATE 'latin1_spanish_ci',
PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

INSERT INTO test (text_accented) VALUES ('Árbol'), ('Café'), ('Cañón');

SELECT *
FROM test
WHERE text_accented = 'arbol';





--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

Tue, 22 Mar 2011 09:26:19 +0100, Álvaro G. Vicario escreveu:

With this collation, searches do not returned similar accented words.
It seems that I should change the collation to 'latin1_spanish_ci'.
Or is there any other possibility?

Thank you,

Luis

El 23/03/2011 0:04, Luis P. Mendes escribió/wrote:
There doesn't seem to be a specific collation for Portuguese:

http://dev.mysql.com/doc/refman/5.1/...-charsets.html

However, the Spanish Latin1 collation appears to handle Portuguese
characters as expected:

INSERT INTO test (text_accented) VALUES ('coração'), ('consciência');

SELECT *
FROM test
WHERE text_accented = 'CORACAO' OR text_accented = 'CONSCIENCIA';

In any case, you should have a look at the available charsets and make
some tests to check that all Portuguese chars are found and you get the
correct alphabetical order when doing an ORDER BY.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

Luis