使用土耳其语字符搜索 [英] Search with Turkish characters

问题描述

我在使用土耳其语大小写的like和elasticsearch进行db搜索时遇到问题.

I have problem on db search with like and elastic search in Turkish upper and lower case.

例如，我有一个帖子表，其中包含标题为 'DENEME YAZI' 的帖子.

For example I have posts table which contains post titled 'DENEME YAZI'.

如果我运行这个查询:

select * from posts where title like '%deneme%';

或:

select * from posts where title like '%YAZI%';

我得到正确的结果，但如果我运行:

I get correct result but if I run:

select * from posts where title like '%yazı%';

它不返回任何记录.我的数据库编码是 tr_TR.UTF-8.如何在不输入确切单词的情况下获得正确的结果?

it doesn't return any record. My database encoding is tr_TR.UTF-8. How can I get correct results without entering exact word?

推荐答案

您必须使用 ILIKE 进行不区分大小写的匹配:

You must use ILIKE for case insensitive matches:

select * from posts where title ilike '%yazı%';

但是，土耳其语语言环境中的特殊规则存在额外的复杂性.'ı' 的大写是 'I'.但反过来不行.'I' 的小写是 'i':

However, there is the additional complication of peculiar rules in the Turkish locale. Upper case of 'ı' is 'I'. But not the other way round. Lower case of 'I' is 'i':

db=# SELECT lower(upper('ı'));
 lower
-------
 i

您可以通过在 LIKE 表达式的任一侧应用 upper() 来解决这个问题:

You could solve that by applying upper() on either side of the LIKE expression:

select upper('DENEME YAZI') like ('%' || upper('yazı') || '%');

这篇关于使用土耳其语字符搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！