0

I have a string column of type NVARCHAR in my table that stores Arabic text contains Arabic letters such as: جهاز ٦٠ للخدمة

The problem is that I cannot retrieve this piece of data when I search for it

declare @textdesc as nvarchar(70) = 'جهاز ٦٠ للخدمة'
Select *
from table1 t1 left join table2 t2 
              on t1.prmkey = t2.frgnkey
where t1.text LIKE '%'+@textdesc+'%'

But when I search by any other field other than @textdesc, it converts the Arabic numbers into English numbers to be 

جهاز 60 للخدمة

in the search results. I don't get the text that is stored in the database with the Arabic numbers. I don't know why this happens. Can you help me figure this out?

I tried to search by 

where @textdesc LIKE N'%' +@textdesc + '%'

and it doesn't work either.

My database collation is Arabic_CI_AS

Ilyes
  • 14,640
  • 4
  • 29
  • 55
MNada
  • 353
  • 2
  • 6
  • 14
  • 2
    Your literal string is a `varchar` not an `nvarchar`, so your variable will have a value like `'??????'`. – Thom A Jul 14 '19 at 11:46
  • 2
    For starters, try `declare @textdesc as nvarchar(70) = N'جهاز ٦٠ للخدمة';` – Dan Guzman Jul 14 '19 at 11:47
  • @DanGuzman Thank you a lot, it works. but why does it work when N in the declaration and doesn't work when it is in the where statement ? – MNada Jul 14 '19 at 11:55
  • 1
    You need to use [`N`](https://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-sql-statements) prefix otherwise SQL Server will treat your string as a varchar. – Ilyes Jul 14 '19 at 11:56
  • 1
    Because of what I mentioned in my comment, @MNada. `'{unicode character}' = '?'`. You **need** to declare your literal string as an `nvarchar` (by prefixing it with an `N`). The `'%'` would be implicitly cast to an `nvarchar` use to data type precedence, as `varchar` has a lower precedence. – Thom A Jul 14 '19 at 12:13
  • 1
    @MNada, without the `N` prefix to denote a Unicode literal, the literal is interpreted as a non-Unicode string. Characters outside the ASCII range are interpretted according to the current database default collation code page and characters with no mapping are either converted to `?` or a fallback character. – Dan Guzman Jul 14 '19 at 12:14
  • Possible duplicate of [LIKE operator, N and % SQL Server doesn't work on nvarchar column](https://stackoverflow.com/questions/54106222/like-operator-n-and-sql-server-doesnt-work-on-nvarchar-column) – Ilyes Jul 14 '19 at 12:50

1 Answers1

1

As a lot of the comments suggest. The trick here is to use the prefix N''. I found a question asking about the N'' prefix where you can read about "What is the meaning of the prefix N in T-SQL statements?", which should solve your problem.

desa
  • 48
  • 8