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

Mansard

Which collation are you using? Is it Turkish_CI_AI? – Smug

I didnt do any collation thing. Should I choose the collation when I create tables? or should I do it when selecting? – Mansard

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. – Smug

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? – Mansard

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). – Smug

Answer 1

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%'

Smug

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 – Mansard

Could you update your original question with the C# code you are using so I could take a look at it? Thanks! – Smug

I used only N and it is working for me. Thanks ! – Superscribe

Answer 2

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

Mansard

Answer 3

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

Peculiar

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. – Gowon

Answer 4

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.

Gladiate