Ignore accents SQLite3
How do I make a SELECT with a LIKE clause ignoring accents on SQL开发者_JAVA百科ite3?
PS: It's for Android build-in SQLite support.
There is a solution, it is not elegant, but it works on Android.
The function REPLACE can replace the accented character by the normal character. Example:
SELECT YOUR_COLUMN FROM YOUR_TABLE WHERE replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace( lower(YOUR_COLUMN), 'á','a'), 'ã','a'), 'â','a'), 'é','e'), 'ê','e'), 'í','i'),
'ó','o') ,'õ','o') ,'ô','o'),'ú','u'), 'ç','c') LIKE 'SEARCH_KEY%'
Or use unicode:
SELECT YOUR_COLUMN FROM YOUR_TABLE WHERE replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace( lower(YOUR_COLUMN), '\u00E1','a'), '\u00E3','a'), '\u00E2','a'), '\u00E9','e'), '\u00EA','e'), '\u00ED','i'),
'\u00F3','o') ,'\u00F5','o') ,'\u00F4' ,'o'),'\u00FA','u'), '\u00E7' ,'c') LIKE 'SEARCH_KEY%'
Where SEARCH_KEY is the key word that you wanna find on the column.
There has been a similar question here.
They said it is not really possible on Android, but there is a workaround with an additional normalized column.
You can a create a mask column and update after insert values in to table with a trigger.
-- Table
CREATE TABLE IF NOT EXISTS mytable (
id TEXT PRIMARY KEY,
description TEXT default '',
description_mask TEXT default ''
);
-- Trigger
CREATE TRIGGER IF NOT EXISTS mytable_in AFTER INSERT ON mytable
BEGIN
UPDATE mytable
SET description_mask =
lower(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
NEW.description,
'á','a'), 'ã','a'), 'â','a'), 'é','e'), 'ê','e'), 'í','i'), 'ó','o') ,'õ','o') ,'ô','o'),'ú','u'),'ç','c'),'ñ','n'),
'Á','a'), 'Ã','a'), 'Â','a'), 'É','e'), 'Ê','e'), 'Í','e'), 'Ó','o') ,'Õ','o') ,'Ô','o'),'Ú','u'),'Ç','c'),'Ñ','n')
)
WHERE id = NEW.id;
END;
-- Select example
SELECT * FROM mytable WHERE (description LIKE '%acido%' OR description_mask LIKE '%ácido%');
精彩评论