如何忽略 SQLite 查询中的重音 (Android) [英] How to ignore accent in SQLite query (Android)
问题描述
我是 Android 新手，正在 SQLite 中处理查询.我的问题是当我在字符串中使用重音时，例如
I am new in Android and I'm working on a query in SQLite. My problem is that when I use accent in strings e.g.
- 阿阿
- ááá
- 咻咻咻
- ààà
- aaa
- AAA
如果我这样做:
SELECT * FROM TB_MOVIE WHERE MOVIE_NAME LIKE '%a%' ORDER BY MOVIE_NAME;
它的回报:
- AAA
- aaa(忽略其他人)
但如果我这样做:
SELECT * FROM TB_MOVIE WHERE MOVIE_NAME LIKE '%à%' ORDER BY MOVIE_NAME;
它的回报:
- ààà(忽略标题ÀÀÀ")
我想在 SQLite 数据库中选择字符串而不关心重音和大小写.请帮忙.
I want to select strings in a SQLite DB without caring for the accents and the case. Please help.
推荐答案
通常，SQL 中的字符串比较由列或表达式COLLATE 规则控制.在 Android 中，只有三个整理序列是预定义:BINARY(默认)、LOCALIZED和UNICODE.它们都不适合您的用例，遗憾的是，Java API 中没有公开用于安装新整理功能的 C API.
Generally, string comparisons in SQL are controlled by column or expression COLLATE rules. In Android, only three collation sequences are pre-defined: BINARY (default), LOCALIZED and UNICODE. None of them is ideal for your use case, and the C API for installing new collation functions is unfortunately not exposed in the Java API.
要解决此问题:
- 向表中添加另一列，例如
MOVIE_NAME_ASCII 将删除重音符号的值存储到此列中.您可以通过将字符串规范化为 Unicode 范式 D (NFD) 并删除非 ASCII 代码点来删除重音，因为 NFD 将重音字符大致表示为纯 ASCII + 组合重音标记:
- Add another column to your table, for example
MOVIE_NAME_ASCII Store values into this column with the accent marks removed. You can remove accents by normalizing your strings to Unicode Normal Form D (NFD) and removing non-ASCII code points since NFD represents accented characters roughly as plain ASCII + combining accent markers:
String asciiName = Normalizer.normalize(unicodeName, Normalizer.Form.NFD)
.replaceAll("[^\p{ASCII}]", "");
是否在此 ASCII 规范化列上搜索文本，但显示原始 unicode 列中的数据.
Do your text searches on this ASCII-normalized column but display data from the original unicode column.
这篇关于如何忽略 SQLite 查询中的重音 (Android)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
