SQLite function that works like the Oracle's "Translate" function?

Question

Oracle has a function called translate that can be used to replace individual characters of the string by others, in the same order that they appear. It is different than the replace function, which replaces the entire second argument occurence by the entire third argument.

translate('1tech23', '123', '456');     --would return '4tech56'
translate('222tech', '2ec', '3it');     --would return '333tith'

I need this to implement a search on a SQLite database ignoring accents (brazilian portuguese language) on my query string. The data in the table that will be queried could be with or without accents, so, depending on how the user type the query string, the results would be different.

Example: Searching for "maçã", the user could type "maca", "maça", "macã" or "maçã", and the data in the table could also be in one of the four possibilities. Using oracle, I would only use this:

Select Name, Id 
  From Fruits 
 Where Translate(Name, 'ãç','ac') = Translate(:QueryString, 'ãç','ac')

... and these other character substitutions:

áéíóúÁÉÍÓÚàèìòùÀÈÌÒÙãõÃÕäëïöüÄËÏÖÜâêîôûÂÊÎÔÛñÑçÇ

by:

aeiouAEIOUaeiouAEIOUaoAOaeiouAEIOUaeiouAEIOUnNcC

Of course I could nest several calls to Replace, but this wouldn't be a good choice.

Thanks in advance by some help.

score 3 · Answer 1 · answered Sep 08 '15 at 13:40

3

Open-source Oracle functions for SQLite have been written at Kansas State University. They include translate() (full UTF-8 support, by the way) and can be found here.

answered Sep 08 '15 at 13:40

user5313140

31
1

This is close to a link only answer. Can you please include more information in case the link changes? – Jason D Sep 08 '15 at 13:48
As such answers like these are generally given as a comment to other people's posts. You can comment once you have gained a bit more reputation. – cfi Sep 09 '15 at 09:57
The correct URL to Kansas State University is: https://sqlite-libs.cs.ksu.edu/ – Chaos Manor Aug 22 '21 at 15:41

score 2 · Accepted Answer · edited May 23 '17 at 11:58

2

I don't believe there is anything in sqlite that will translate text in a single pass as you describe.

This wouldn't be difficult to implement as a user defined function however. Here is a decent starting reference.

edited May 23 '17 at 11:58

Community

1
1

answered Dec 19 '12 at 19:06

Tom Kerr

10,444
2
30
46

score 0 · Answer 3 · answered Apr 16 '20 at 14:46

0

I used replace

REPLACE(string,pattern,replacement)

https://www.sqlitetutorial.net/sqlite-replace-function/

answered Apr 16 '20 at 14:46

Kylo Jorgensen

11
2

Thanks @Kylo, but the question is about to replace each char of the "pattern" by the corresponding char in "replacement". For using what you suggested I would need to nest several calls to REPLACE. Anyway, thank you by the answer. – Douglas Lise Apr 16 '20 at 23:20

SQLite function that works like the Oracle's "Translate" function?

3 Answers3