Ignore accents SQLite3

Question

How do I make a SELECT with a LIKE clause ignoring accents on SQLite3?

PS: It's for Android build-in SQLite support.

Derzu · Answer 1 · 2018-07-26T20:27:13.027

13

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.

edited Jul 26 '18 at 20:27

answered Oct 25 '12 at 05:42

Derzu

7,011
3
57
60

2

Also this ins't very good for performance, but at least is a solution – Marcos Vasconcelos Oct 26 '12 at 15:59
@MarcosVasconcelos Yes, it's not good in performance. But it is a possible solution. – Derzu Oct 26 '12 at 16:56
1

Could we reduce the number of "replace" function in the query string by using REGEX in replace function call ? – TuanPM Jan 21 '20 at 03:47
@TuanPM, I think maybe possible to use regex, but I don't know-how. If you know please share here. – Derzu Jan 21 '20 at 12:23
@Derzu unfortunately, i don't know too. By the way, too many "replace" function may lead to "SQLiteException: parser stack overflow" when running on Android. – TuanPM Jan 22 '20 at 09:56
MySQL supported functions. I wonder: does SQLite do so as well? If it did, these ["train wrecks"](https://www.thinkcode.se/blog/2011/12/30/how-many-train-wrecks-are-lurking-in-your-code) could be isolated into one single function... right? However, even if SQLite in Android supported SQL custom functions, maybe RoomDB does not... T_T – SebasSBM Apr 01 '22 at 01:27
PD: I can confirm this train wreck triggers an error in RoomDB pre-building... so I didn't try to build it at all... even though I confirmed this approach works in raw SQLite 3 queries... what a shame... T_T – SebasSBM Apr 01 '22 at 02:20

score 1 · Accepted Answer · edited May 23 '17 at 12:09

1

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.

edited May 23 '17 at 12:09

Community

1
1

answered Mar 30 '11 at 21:26

Karsten

1,814
2
17
32

Agreed. That was what I applied in the first implementations for this in my project using RoomDB. For a single column, it's fine. However, I am looking for an alternative for One2Many relationships; doing a parallel "normalized children table" looks overkill for me, in my case. What a shame... I'd rather not have to copy the [train wreck](https://www.thinkcode.se/blog/2011/12/30/how-many-train-wrecks-are-lurking-in-your-code) in [the answer above](https://stackoverflow.com/a/13062285/3692177) for `JOIN` statements lol – SebasSBM Apr 01 '22 at 01:20

score 0 · Answer 3 · answered Nov 20 '19 at 23:36

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%');

Ignore accents SQLite3

3 Answers3

Linked