在SQL Server中以不同的编码搜索波斯字符和单词 [英] Searching Persian characters and words in SQL server with different encoding

问题描述

我有一个包含波斯语单词的文本文件，并且该文件使用ANSI编码保存.当我尝试从文本文件中读取波斯语单词时，出现了诸如?"之类的字符.为了解决该问题，我编写了一种方法，将文件编码更改为UTF8，然后重新编写文本文件. 方法:

I have a text file that contains Persian words and the file is saved using ANSI encoding. when I try to read the Persian words from the text file, I get some characters like '?'. In order to solve the problem, I wrote a method that change the file encoding to UTF8 and re-write the text file. the method:

    public void Convert2UTF8(string filePath)
    {
        //first, read the text file with "ANSI" endocing
        StreamReader fileStream = new StreamReader(filePath, Encoding.Default);
        string fileContent = fileStream.ReadToEnd();
        fileStream.Close();
        //Now change the file encoding and replace it with the UTF8
        StreamWriter utf8Writer = new StreamWriter(filePath.Replace(".txt", ".txt"), false, Encoding.UTF8);
        utf8Writer.Write(fileContent);
        utf8Writer.Close();
    }

现在第一个问题解决了；但是，主要问题在这里: 我想将单词插入SQL Server数据库的表中.我这样做，但是每次我想从数据库表中搜索波斯语单词时，结果都为空，而记录确实存在于数据库表中. 找到表中存在的波斯语单词的解决方案是什么? 我当前使用的代码就像:

Now the first problem is solved; However, the main issue is in here: I want to insert the words into a table in SQL server database. I do this, but every time that I want to search a Persian word from the database table, the result is null while the record does exist in database table. What`s the solution to find my Persian words that exist in table. The code that I currently use is simply like:

SELECT * FROM [dbo].[WordDirectory] 
WHERE Word = N'کلمه'

'Word'是保存波斯语单词的字段.该字段的类型为NVARCHAR.我的SQL Server版本是2012. 我应该更改排序规则还是什么?

'Word' is the field that Persian words are saved in. The type of the field is NVARCHAR. My SQL server version is 2012. Should I change the collation or something?

推荐答案

DECLARE @Table TABLE(Field NVARCHAR(4000) COLLATE Frisian_100_CI_AI)

INSERT INTO @Table (Field) VALUES
(N'همهٔ افراد بش'),
(N'می‌آیند و حیثیت '),
(N'ميشه آهسته تر صحبت کنيد؟'),
(N'روح'),
(N' رفتار')   

SELECT * FROM @Table
WHERE Field LIKE N'%آهسته%'

两个查询返回相同的结果

The both Queries return the same result

RESULT Set:  ميشه آهسته تر صحبت کنيد؟

您必须确保在插入值时添加前缀，然后使用N告诉SQL Server传递的字符串中可以包含Unicode字符.在Select语句中搜索字符串时，情况也是如此.

You have to make sure that when you are inserting the values you prefix then witn N thats to tell sql server there can be unicode character in the passed string. Same is true when you are searching for them strings in Select statement.

这篇关于在SQL Server中以不同的编码搜索波斯字符和单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！