0

I have a problem in SQL with NVARCHAR type and Persian char 'ی' and 'ک', I have some records in a Table like:

+----- Name ------+
+----- علی ------+

When I want to select from this Table like:

select * from [Table] when Name like 'علی'
select * from [Table] when Name='علی'
select * from [Table] when Name like 'علي'
select * from [Table] when Name='علي'

It returns NULL! I found that when I use N before strings it is solved but I need to use N before parameter in SP and try this:

declare @name nvarchar(max)='علی'
select * from [Table] when Name like N''+@name

But unfortunately it is not working and I found when I assign 'علی' to the nvarchar, automatically 'ی' converted to 'ي'!!!

How can I fix that?

wakiwiki
  • 231
  • 4
  • 13
  • Possible duplicate of [SQL Select Command with Unicode string is not Retrieving the Expected Data](https://stackoverflow.com/questions/14411689/sql-select-command-with-unicode-string-is-not-retrieving-the-expected-data) – Igor Jul 02 '19 at 12:39
  • See also [The 'N' in the where clause of SQL](https://stackoverflow.com/q/18521928/1260204) – Igor Jul 02 '19 at 12:40
  • You should normalize your data to have only Persian yeh and keh and not Arabic yeh and keh. more info in Persian: https://www.dotnettips.info/post/90 – VahidN Jul 02 '19 at 16:51

3 Answers3

0

It seems like you're using MS SQL Server. In that case the N prefix denotes a unicode string.

Thus, you should use N'*' whenever you set a unicode string. Therefore, you should change your stored procedure like so:

declare @name nvarchar(max)=N'علی'
select * from [Table] when Name like @name
Dominik
  • 55
  • 1
  • 6
  • It is ok, but not things that I want, I want to give this @name parametrically from SP, it comes from C# – wakiwiki Jul 02 '19 at 12:43
0

1- Search and find the best Collation and Unicode for your database. It can be different in each case.

2- If you are using a programming language, you can handle it there. When you want to save a record, you should change ی ک ه and unify them. It means you should always use the same char. In save, update and select(in where clause).

3- You can make a trigger for Insert and unify characters.

Saeid Amini
  • 1,313
  • 5
  • 16
  • 26
-1

You don't need to use N before a parameter that is Nvarchar. This will work :

declare @name nvarchar(max)='علی' -- or maybe you want  '%علی%'
select * from [Table] when Name like @name
nAviD
  • 2,784
  • 1
  • 33
  • 54
  • No, this will not work (or, at least, it's not guaranteed to, depending on your database collation). The `N` in front of the *string* literal is still required, and that's already covered in another answer. SQL Server does not change the type of a literal based on how it's used; this implicitly converts a `VARCHAR(3)` that more likely than not consists of just `???`. – Jeroen Mostert Jul 05 '19 at 12:17