SQL Server turkish character situation

Question

I am having a Turkish Character problem in ASP.NET and SQL Server. I have searchbox in asp.net and trying to do a search in database. However, I am having problems in Turkish characters. When I search for "GALVANİZ" which contains illegal "İ" character. the word "GALVANİZ" is in the database, I am sure.

When I do a simple select statement in SQL Server tool, it doesn't return anything either.

Here is the SQL

select * from Product where name like '%GALVANİZ%'

This doesn't return anything. How can I get it fixed?

thanks

I didnt do any collation thing. Should I choose the collation when I create tables? or should I do it when selecting? — Arif YILMAZ, Mar 27 '13 at 20:41
You can do either. You could also try your query like this `where name like N'%GALVANİZ%'` with N in front of the characters to indicate Unicode. — Martin, Mar 27 '13 at 20:42
thank you very much. it works but can you give me a more proper way to do it? what is the best way to do this? — Arif YILMAZ, Mar 27 '13 at 20:44
Ensure that you use unicode text in your queries (by prepending the 'N' character before the string) in order to indicate that the text should be treated as Unicode. I will add this is a proper answer (so you can mark it as answered). — Martin, Mar 27 '13 at 20:45

score 17 · Accepted Answer · answered Mar 27 '13 at 20:46

17

You can specify a collation in your query such as Turkish_CI_AI, or alternatively use the 'N' character with your strings to indicate that they are Unicode, as so:

select * from Product where name like N'%GALVANİZ%'

answered Mar 27 '13 at 20:46

Martin

16,093
1
29
48

I will mark it in 5 mins. when I do the select in mssql tool, it works. but when I do it in C# code, it doesnt work. why do you think it doesnt work in c# code – Arif YILMAZ Mar 27 '13 at 20:48
Could you update your original question with the C# code you are using so I could take a look at it? Thanks! – Martin Mar 27 '13 at 20:49
1

I used only N and it is working for me. Thanks ! – Ramil Aliyev 007 Dec 22 '22 at 14:15

score 5 · Answer 2 · answered Mar 27 '13 at 21:17

5

I solved the question myself too. here is the solution

select * from product where name like N'%GALVANi%' collate Turkish_CI_AS

this is a much better solution

answered Mar 27 '13 at 21:17

Arif YILMAZ

5,754
26
104
189

score 0 · Answer 3 · answered Sep 11 '21 at 14:51

0

sudo systemctl stop mssql-server sudo /opt/mssql/bin/mssql-conf set-collation enter collation: Turkish_CI_AS sudo systemctl start mssql-server

answered Sep 11 '21 at 14:51

mert özakus

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-ask). – Community Sep 11 '21 at 15:34

score 0 · Answer 4 · answered Jan 26 '23 at 21:37

Yes, putting N in front of the search text will work. But in such a case you will have to use N constantly.

If you want it to find unicode characters in all cases (Turkish, Russian, Greek, Japanese, Arabic, etc.), your choice for DB Collation should be the UTF8 ones for the latin series. for example: Latin1_General_100_CI_AS_KS_SC_UTF8

This way you don't have to use the prefix N when searching.

SQL Server turkish character situation

4 Answers4