Query Chinese characters in SQL server from Access

Question

There are two Chinese words 村 and 邨. both meaning village.
when create a table In Access and store these two words, CREATE TABLE table1 ( field1 shorttext )
then add 2 records one for 村 and one for 邨.
for example, SELECT * FROM table1 WHERE field1="村"
i can query this table to get result, no matter i query field1 = 村 or 邨.

i create the same table table1 in SQL server with the field1 nvarchar(10).
then add 2 records one for 村 and one for 邨.
in Access, i use Linked Table to connect to SQL server to get table1.
when i query SELECT * FROM table1 WHERE field1="村", it returns result.
but if query SELECT * FROM table1 WHERE field1="邨", it returns nothing.
i try SELECT * FROM table1 WHERE field1=N'邨', Access returns syntax error.

Does it relate to collation?
How can I set SQL server collation to Windows collation to test?
how can i do query from Access?

Answer 1

Hi @UOG365Admin ,

Welcome to Microsoft Q&A!

i try SELECT * FROM table1 WHERE field1=N'邨'

N'xxx' indicates that the xxx in the single quotation mark is forced to be converted into a Unicode-encoded character, without garbled characters in Chinese.
First let me state that I'm not very familiar with Access, I'm more familiar with SQL Server. Since there is no problem with '村', I don't think it's a problem with the SQL Server collation at least. This statement is also fine, so I recommend taking a look at the documentation on special characters in Access here. There is a possible workaround at the end of the document.
Please read this MS Doc: Error message when you use special characters in Access databases.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If you do not find a solution, then consider an alternative: “Pass-Through Queries”, and use ‘N’:

 SELECT * FROM table1 WHERE field1 = N'村'

See: https://support.microsoft.com/en-us/office/create-a-pass-through-query-b775ac23-8a6b-49b2-82e2-6dac62532a42. Copy the connection string from Linked Table Manager or DSN file as explained in documentation.

Probably it is advantageous because the large tables will not be transferred to Access before applying the filters.

Answer 3

This works for me in SQL Server:

CREATE TABLE #temp (a nvarchar(10) NOT NULL)
INSERT #temp (a) VALUES(N'村'), (N'邨')
SELECT * FROM #temp WHERE a = N'村'
SELECT * FROM #temp WHERE a = N'邨'
DROP TABLE #temp

I find both characters.

If you want to know what Access is up to, you can use Profiler to see what queries that Access emits.

If you want help with Access, you should add a tag for Access. Myself, I don't know Access at all.

Answer 4

Hi ErlandSommarskog,
I try your code in SQL.
Whenever a field is nvarchar, insert or query the field always use N'xxx' then we can get the result.
We don't need to consider the SQL or Windows collation. is it ture?

Answer 5

Whenever a field is nvarchar, insert or query the field always use N'xxx' then we can get the result.

Yes, the N is important. The N makes the data type of the string to be nvarchar. Without the N, it will be varchar. If I say:

SELECT N'村', N'邨'

It get back the Chinese characters. If I say:

SELECT '村', '邨'

What I get depends on the collation, and more precisely the code page of the collation. With varchar, you only have access to the characters in a specific code page.

Now, as it happens, when I run this statement on my SQL 2019 instance, I still see the Chinese characters, because I have a UTF8 collation. That is, I still have full Unicode support.

But if I run without the N on any of my other instances, I get back question marks, because the older versions do not support UTF-8 and I have a Finnish_Swedish collation with a code page for Swedish characters, and which do not support Chinese characters. Although to be precise, it depends on the database collation. So on an SQL 2014 instance I found a database with the collation Korean_Wansung_CI_AS, and when I run:

SELECT '村', '邨'

I do get back the Chinese characters.

We don't need to consider the SQL or Windows collation. is it ture?

Here things get a little complicated (just in case you don't think that they already are!). For best support of Chinese, I think you should use a collation which includes SC in the name, even if you are using nvarchar.

This is because there are quite a few Chinese characters that are beyond the Unicode base plane, that is the character codes up to 65535. Now, as I understand, these are "fancy", "unusual" or "historic" characters that you don't need for daily use of Chinese. But please beware that I don't know Chinese. Anyway, if you would have to use any of these "special" Chinese characters, a collation without _SC would not handle them correctly in some situations. Plain equality should work, I think, but there are other operations that can go wrong.

But apart from that, it does not matter if you have Chinese, French or Bengali collation if you are using nvarchar.