查询拉丁表中外语字段的排序规则 [英] query collation on foreign language field in Latin table

问题描述

我有一系列表，每个表都有专门的外语列。语言有日文，泰文，英文，意大利文，法文，共有20种以上。

I have a series of tables that each have a dedicated column to a foreign language. Languages vary from Japanese, Thai, English, Italian, French, more than 20 in all.

所有这些表均使用拉丁文不区分大小写的排序规则进行设置。 DB工作正常。

All of these tables are set up with Latin Case Insensitive collation. DB works fine.

但是现在我试图针对每个表的特定外语列进行查询。让我们以日语作为入门。我想让外语用户输入外语文本并根据外语列查找记录。

But now I am trying to query against the specific foreign language column of each table. Lets take Japanese for starters. I'd like a foreign language user to enter foreign text and find the record based on the foreign language column.

  DECLARE @myVar nvarchar(max);

  SET @myVar =  'ｴﾝｼﾞﾝ ｽﾄｯﾌﾟ ﾘﾚｰ'      = 'Engine Stop Relay' in english

  Select *
  FROM tableJapanese
  WHERE langString = @myVar;

我尝试了多种排序规则组合。我什至复制了该表，并将该列的排序规则更改为Japanese_CI_AI，并尝试以这种方式进行查询。

I have tried a multitude of collation combinations. I even copied the table and changed the collation of the column to Japanese_CI_AI and tried to query it that way.

这些WHERE子句都不适用于表/列排序规则当列是拉丁文还是日文...

None of these WHERE clauses work on either table/columm collation, when the column was Latin or Japanese...

WHERE lang_String collate Japanese_CI_AI = @myVar;
WHERE lang_String = @myVar collate Japanese_CI_AI;
WHERE lang_String collate Japanese_CI_AI = @myVar collate Japanese_CI_AI;

WHERE lang_String collate Japanese_CI_AI = @myVar;
WHERE lang_String = @myVar collate Japanese_CI_AI;
WHERE lang_String collate Japanese_CI_AI = @myVar collate Japanese_CI_AI;

我想保留列/数据库为拉丁文排序规则，并在可能的情况下为每种语言编写查询代码。

I would like to leave the columns/database as Latin collation and code the queries for each language if possible.

这似乎是我被蛇咬过的问题之一。谁能看到我缺少的东西吗？

This seems like one of those problems that if were a snake I'd been bitten already. Can anyone see what I am missing?

MSSQL Express 2008 R2

MSSQL Express 2008 R2

解决方案 ：

在字段前面添加N，表示将unicode转换为SQL ...

Add N in front of the field, it indicates unicode to SQL...

  Select *
  FROM tblLangJAP_test
  WHERE lang_String = N'ｴﾝｼﾞﾝ ｽﾄｯﾌﾟ ﾘﾚｰ';

工作完美。

谢谢，

推荐答案

在字段前面添加N，它表示将unicode转换为SQL ...
选择*
FROM tblLangJAP_test
WHERE lang_String = N'ｴ ﾝ ｼﾞ ﾝ ﾘ ﾚ ｰ';

Add N in front of the field, it indicates unicode to SQL... Select * FROM tblLangJAP_test WHERE lang_String = N'ｴﾝｼﾞﾝ ｽﾄｯﾌﾟ ﾘﾚｰ';

完美运行。

这篇关于查询拉丁表中外语字段的排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！